laravel 4 - how to Limit (Take and Skip) for Eloqu

问题:

TL;DR

Can you limit an Eloquent ORM query like using take() and skip() so that the resulting mysql query is also limited, and it doesn't have to return the entire dataset?

If so, how would you modify:

$test = User::find(1)->games->toArray();

To include limit 3 offset 2?

---

Tables:

users       games           userGames
-- id       -- id           -- user_id
-- name     -- name         -- game_id
            -- steam_id

Models:

class User extends Eloquent {
    public function games() {
        return $this->belongsToMany('Game', 'userGames', 'user_id', 'game_id');
    }
}

class Game extends Eloquent {
    public function users() {
        return $this->belongsToMany('User', 'userGames', 'user_id', 'game_id');
    }
}

---

Limit in Query Builder

Using the regular Laravel Query Builder I can get all games that belong to user of id 1, and limit the result with take() and skip():

$test = DB::table('games')
    ->join('userGames', 'userGames.game_id', '=', 'games.id')
    ->where('userGames.user_id', '=', '1')->take(3)->skip(2)->get();

By listening to the illuminate.query event I can see that the query generated by this is:

select * from `games`
inner join `userGames`
on `userGames`.`game_id` = `games`.`id`
where `userGames`.`user_id` = ?
limit 3 offset 2

Limit in Eloquent ORM

When I try to recreate the same query with Eloquent:

$test = User::find(1)->games->take(2)->toArray();

I'm able to use take but adding skip causes an error. Also the resulting query does not actually contain the limit:

select `games`.*, `userGames`.`user_id` as `pivot_user_id`,
`userGames`.`game_id` as `pivot_game_id` from `games`
inner join `userGames`
on `games`.`id` = `userGames`.`game_id`
where `userGames`.`user_id` = ?

So it seems that the entire result is being queried first, which is not ideal when dealing with large data sets.

---

Question:

Is it possible to limit an Eloquent ORM query so that at the MYSQL Query level it also limits the result, equivalent to limit 3 offset 2?

回答1:

User::find(1)->games()->take(3)->skip(2)->get();

I think this should give you your collection. :)

->games will give you a collection, where ->games() will offer a query builder instance.

Enjoy Laravel!