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
?
users games userGames
-- id -- id -- user_id
-- name -- name -- game_id
-- steam_id
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');
}
}
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
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.
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
?
Limit and offset in Laravel is used to paginate records or get the number of records from the table from an offset. In this example, you will see how to use/ set the limit and offset in Laravel Framework. $condition=[]; $offset=0 // start row index. $limit=100 // no of records to fetch/ get .
Laravel limit accepts one parameter as count( number of count). Model::limit(10); //or \DB::table('table_name')->limit(10); In the above syntax we have used 1 examples to show possibilities to use limit function in laravel. Also we can use offset function in laravel to define the starting point in query to limit.
take() will help to get data from a database table with a limit. skip() will help to skip some records when you fetch data from the database table. So, let's see bellow examples that will help you how to use take() and skip() eloquent query in laravel. Example: take()
Laravel includes Eloquent, an object-relational mapper (ORM) that makes it enjoyable to interact with your database.
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With