laravel 4 - how to Limit (Take and Skip) for Eloquent ORM?
Stefan Bogdanescu
Founder & Senior Architect · 2026-06-29
# Laravel Eloquent: How to Limit (Take and Skip) for Eloquent ORM?
## 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:
```php
$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
```php
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 (The Foundation)
When working directly with the Laravel Query Builder, applying limits and offsets is straightforward because you are operating directly on the underlying SQL construction. This approach ensures maximum control over the generated query.
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()`:
```php
$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, we can see that the query generated by this is precisely what we intended:
```sql
select * from `games`
inner join `userGames`
on `userGames`.`game_id` = `games`.`id`
where `userGames`.`user_id` = ?
limit 3 offset 2
```
This demonstrates that the Query Builder provides direct access to methods like `limit()` and `offset()`, which translate perfectly into native SQL commands.
## Limit in Eloquent ORM (The Challenge)
When I try to recreate the same logic using Eloquent relationships:
```php
$test = User::find(1)->games->take(2)->toArray();
```
I encounter significant issues. While I can successfully use `take()` on the relationship, adding `skip()` causes an error or unpredictable behavior. More importantly, the resulting query does not actually contain the `LIMIT` or `OFFSET` clauses necessary to restrict the data retrieved from the database layer.
The generated SQL remains:
```sql
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` = ?
```
This indicates that Eloquent, when traversing relationships like `belongsToMany`, often fetches the entire related set first before applying filtering or limiting at the final presentation layer. This is inefficient and defeats the purpose of pagination on large datasets.
## The Solution: Limiting via Constraints and Eager Loading
The core challenge lies in how Eloquent handles relationship loading versus database restriction. Since standard Eloquent methods do not expose direct `limit`/`offset` methods on relationships, we must leverage the Query Builder capabilities within our Eloquent context to enforce these constraints at the source.
To achieve true MySQL-level limiting with Eloquent, we must bypass the simple relationship accessor and build the query directly using scoping or constrained eager loading.
Here is the recommended approach for implementing pagination on many-to-many relationships:
```php
use Illuminate\Support\Facades\DB;
// Define the desired offset and limit
$offset = 2;
$limit = 3;
$userId = 1;
// Build the query using the Query Builder directly, which respects LIMIT/OFFSET
$games = DB::table('games')
->join('userGames', 'userGames.game_id', '=', 'games.id')
->where('userGames.user_id', '=', $userId)
->orderBy('games.id') // Essential for consistent ordering with limit/offset
->limit($limit)
->offset($offset)
->get();
// If you need to attach this result back to the User model context,
// you would typically perform a separate load or use advanced query scopes.
```
This method ensures that the database engine handles the restriction (`LIMIT` and `OFFSET`), which is far more efficient than fetching all records into PHP memory and then trimming them. While Eloquent provides beautiful syntactic sugar for ORM operations, complex pagination logic involving direct row limiting often requires dropping down to the underlying Query Builder when dealing with many-to-many relationships. For deeper dives into optimizing data retrieval within the Laravel ecosystem, exploring how Eloquent integrates with raw SQL is always a valuable step, as seen in advanced patterns discussed on platforms like https://laravelcompany.com.
## Conclusion
While Eloquent is an incredibly powerful tool for object-relational mapping, understanding the boundary between ORM abstractions and direct database operations is crucial for performance tuning. For simple CRUD operations, Eloquent excels. However, when implementing complex features like pagination or row limiting on many-to-many relationships, relying solely on Eloquent's relationship methods often fails to provide the necessary SQL-level control. By strategically utilizing the Laravel Query Builderâas demonstrated aboveâwe gain explicit control over `LIMIT` and `OFFSET`, resulting in highly optimized queries that respect the principles of efficient data retrieval.