The listing API — pagination, filters, and N+1
An endpoint that returns a list is the first place that gets slow in production.
During development the seeder gives you only 20 rows, so anything you write returns quickly. The problem only shows up once the row count grows. This chapter builds a shape from the start that does not break as the count grows.
An API that returns everything is guaranteed to break
Look back at the product listing from chapter 4.
public function index()
{
return Product::all();
}
Product::all() loads every row of the table into memory. Twenty products is fine; 100,000 products means reading 100,000 rows.
Three things happen.
- Out of memory — hitting PHP's memory limit and the process dies
- A bloated response — tens of megabytes of JSON come back and clog the receiving side too
- Database load — reading every row makes other queries wait
And all three happen at once. Somewhere along the way as products accumulate, one day it simply errors.
Adding pagination
Put an upper bound on how many rows come back.
public function index()
{
$products = Product::paginate(20);
return ProductResource::collection($products);
}
Create ProductResource the same way as the previous chapter's OrderResource (php artisan make:resource ProductResource). Start with something that returns just id, name, and price.
Combined with the previous chapter's Resource, the response looks like this.
{
"data": [
{ "id": 1, "name": "...", "price": 12800 }
],
"links": {
"first": "http://localhost:8000/api/v1/products?page=1",
"last": "http://localhost:8000/api/v1/products?page=5",
"prev": null,
"next": "http://localhost:8000/api/v1/products?page=2"
},
"meta": {
"current_page": 1,
"from": 1,
"to": 20,
"per_page": 20,
"total": 87,
"last_page": 5
}
}
Here is where the previous chapter's decision to keep data wrapping pays off. links and meta sit somewhere that does not mix with the product fields.
The client learns the total from meta.total and the next page's URL from links.next. It never has to compute the next page number itself.
Choosing between the three kinds of pagination
Laravel offers three approaches. Choose by row count and use case.
| Method | Total count | Page numbers | Deep pages |
|---|---|---|---|
paginate() | Counted | Available | Get slow |
simplePaginate() | Not counted | Available | Get slow |
cursorPaginate() | Not counted | Not available | Stay fast |
paginate — when you need the total
Showing "1-20 of 87" or rendering page number links requires the total. paginate() gets it with a COUNT query.
The cost is two queries per request: the SELECT for the data and the COUNT for the total. As the table grows, the COUNT gets heavy.
simplePaginate — when "next" is all you need
If you do not need the total, you can skip the COUNT1. It suits situations like infinite scroll where knowing "is there more?" is enough.
$products = Product::simplePaginate(20);
total and last_page disappear from meta, and last disappears from links.
cursorPaginate — when deep pages must stay fast
Both paginate() and simplePaginate() use OFFSET. There is a trap there.
-- Page 2 (offset 15)
select * from users order by id asc limit 15 offset 15;
-- Page 1000 (offset 14985)
select * from users order by id asc limit 15 offset 14985;
OFFSET actually reads the rows it skips and then discards them. Fetching page 1000 means reading and throwing away 14,985 rows before returning 15. The deeper the page, the slower it gets.
The cursor approach avoids this1.
-- The cursor approach
select * from users where id > 15 order by id asc limit 15;
"Continue from last time" is expressed in the WHERE, so the number of rows read is the same at any page.
$products = Product::orderBy('id')->cursorPaginate(20);
In exchange, you cannot specify a page number. Jumping to page 5 is out; you only move forward and back. Choose based on whether your API's consumers need page numbers.
The product listing uses paginate(). Up to tens of thousands of rows the COUNT cost is acceptable, and showing a total is natural. For an API returning data that grows without bound, such as logs, consider cursorPaginate().
Accepting filters and sorting through an allow list
A listing wants filtering and sorting.
GET /api/v1/products?keyword=coffee&sort=price&direction=asc
Written plainly, that becomes this.
public function index(Request $request)
{
$query = Product::query();
if ($request->filled('keyword')) {
$query->where('name', 'like', '%' . $request->input('keyword') . '%');
}
$query->orderBy($request->input('sort', 'id'), $request->input('direction', 'asc'));
return ProductResource::collection($query->paginate(20));
}
The $request->input('sort') passed to orderBy() is the problem. The client can name any column at all.
An input like ?sort=internal_cost sorts by a column you never published. The values themselves are not visible because the Resource filters them, but the ordering reveals their relative sizes. In other words, someone can sort by cost, highest first.
Accept them through an allow list.
public function index(Request $request)
{
$validated = $request->validate([
'keyword' => ['sometimes', 'string', 'max:100'],
'sort' => ['sometimes', 'in:id,name,price,created_at'],
'direction' => ['sometimes', 'in:asc,desc'],
'per_page' => ['sometimes', 'integer', 'min:1', 'max:100'],
]);
$query = Product::query();
if (isset($validated['keyword'])) {
$query->where('name', 'like', '%' . $validated['keyword'] . '%');
}
$query->orderBy(
$validated['sort'] ?? 'id',
$validated['direction'] ?? 'asc',
);
return ProductResource::collection(
$query->paginate($validated['per_page'] ?? 20)
);
}
The in: rule enumerates the values that may be specified. A column name not in the list is rejected with a 422.
per_page also has an upper bound. Without one, ?per_page=999999 amounts to fetching everything. That defeats the point of adding pagination.
Making N+1 happen and observing it
Say you want stock quantity in the product listing too.
public function toArray(Request $request): array
{
return [
'id' => $this->id,
'name' => $this->name,
'price' => $this->price,
'stock_quantity' => $this->stock->quantity, // added
];
}
It works. But look at the queries it issues and the problem becomes clear.
select count(*) from products; -- paginate counting the total
select * from products limit 20 offset 0; -- fetching 20 products
select * from stocks where product_id = 1; -- stock for #1
select * from stocks where product_id = 2; -- stock for #2
...
select * from stocks where product_id = 20; -- stock for #20
Twenty-two queries went out. That is one COUNT from paginate, one to fetch the products, and 20 to fetch each product's stock. The first two are unavoidable; the last 20 are the problem.
This is the N+1 problem: one query (1) plus one query per result row (N). The cause is that reading a relationship as a property fires a SELECT at that moment2. This is called lazy loading.
You will not notice locally. Twenty queries against local SQLite are instantaneous. In production with the database on a separate server, each one adds a network round trip. At 2 milliseconds each, 20 queries cost 40 milliseconds, and with per_page=100 you lose 200 milliseconds right here.
Seeing it for yourself
Laravel can log queries.
DB::enableQueryLog();
$products = Product::paginate(20);
ProductResource::collection($products)->toArray(request());
dump(count(DB::getQueryLog())); // 22
Not "it feels slow" — count the queries, then fix it.
Killing it with eager loading
Load the relationships you need up front.
$products = Product::with('stock')->paginate(20);
The queries become this.
select count(*) from products;
select * from products limit 20 offset 0;
select * from stocks where product_id in (1, 2, 3, ..., 20);
Twenty-two became three. The stock query dropped from 20 to 1. It stays at three for 20 rows and for 100. Because it asks with an IN clause, the query count does not change as the row count grows.
This is why the previous chapter used whenLoaded.
'stock_quantity' => $this->whenLoaded('stock', fn () => $this->stock?->quantity ?? 0),
The ?-> is there because products with no stock row can exist. The chapter 3 schema does not enforce "every product has a stock row." Mix in a single such product and an unchecked $this->stock->quantity turns the whole listing API into a 500.
stock_quantity appears only when you loaded it with with('stock'). Forget the load and the key disappears entirely, so the query count never climbs back to 21. Instead the field vanishes from the response, which a test can catch.
When you only need a count, use withCount
For an aggregate like "how many lines are there" rather than "the stock quantity," you do not need to read the whole relationship.
$orders = Order::withCount('items')->paginate(20);
// available as $order->items_count
It comes back with an added items_count column. The line records themselves are never read, so there is less data.
Detecting N+1 mechanically
Forget to write with() and the code still works. Nothing errors, so an oversight in review reaches production.
Laravel has a setting that throws when lazy loading occurs2.
use Illuminate\Database\Eloquent\Model;
public function boot(): void
{
Model::preventLazyLoading(! $this->app->isProduction());
}
The ! $this->app->isProduction() is the point. Enable it everywhere except production.
- Local and test environments: lazy loading throws. You notice during development
- Production: no exception. Even with a missing load, it keeps working, just slower
Make it throw in production too and one forgotten with() turns an endpoint into a 500. Slow is preferable.
With this setting in place, the tests we write in chapter 10 double as an N+1 detector. Tests passing means no lazy loading is happening.
What matters in production
Put an upper bound on per_page
As written above, but it is important enough to repeat. If your design lets the client choose the row count, always set an upper bound.
Without one, a single request can fetch everything. Even with no malicious intent, a client-side implementation mistake (putting the total count into per_page) does the same thing.
LIKE with a leading wildcard defeats indexes
$query->where('name', 'like', '%' . $keyword . '%');
A search with % on both ends cannot use an index. It examines every row of the table one at a time. Fine for a few thousand products; at a few hundred thousand, one search takes seconds.
There are three remedies. A prefix match ($keyword . '%') lets an index be used. You can use the database's full-text search. If you need it properly, you stand up a dedicated search engine.
Which one you choose depends on your data volume. Index design itself is covered by the Database Design Guide.
Do not pile relationships into a listing
Grow it to with('stock', 'category', 'reviews', 'tags') and the queries and the payload grow with it. Even with N+1 solved, the data returned in one request gets heavy.
Keep listings minimal and expand things in the detail endpoint. The point of a listing is to learn what exists, not to learn everything.
Make the sort order deterministic
$query->orderBy('price')->paginate(20);
With several products at the same price, the order across pages is not guaranteed. A product on page 1 can reappear on page 2, or never appear on any page.
Add a column that decides ties.
$query->orderBy('price')->orderBy('id')->paginate(20);
id never repeats, so the order is pinned down. It matters even more with cursorPaginate(), where the cursor calculation depends on the sort key.
Summary
Model::all()reads every row into memory. Always add pagination to a listing APIpaginatecounts the total (two queries),simplePaginatedoes not, andcursorPaginatestays fast on deep pages at the cost of page numbers- Accept sort column names through an allow list. Allow arbitrary columns and the ordering reveals unpublished values
- Without an upper bound on
per_page, adding pagination is pointless - Reading a relationship as a property fires a query on the spot (N+1). Load it first with
with() - Enabling
preventLazyLoadingeverywhere except production surfaces missing loads as exceptions during development - Add
idto a sort with possible ties so the order is deterministic
What to read next
The next chapter, Error design — translating exceptions into HTTP responses, tidies up the error responses we have written ad hoc so far. What to return when stock is insufficient, and what not to return when an unexpected exception occurs. We make withExceptions in bootstrap/app.php the one and only conversion point and standardize the shape.
Exercises
The following listing API lets unpublished information be inferred. Where?
public function index(Request $request)
{
$validated = $request->validate([
'sort' => ['sometimes', 'string'],
'direction' => ['sometimes', 'in:asc,desc'],
]);
return ProductResource::collection(
Product::orderBy($validated['sort'] ?? 'id', $validated['direction'] ?? 'asc')
->paginate(20)
);
}
Sample answer
The rule for sort is only string, so any column name gets through.
Suppose the products table has internal_cost. ProductResource does not publish it, so the value itself is not returned. But this request goes through.
GET /api/v1/products?sort=internal_cost&direction=desc
The products come back ordered by cost, highest first. Even without seeing the values, you learn the ranking. Page through and you have the cost ranking for every product; know a few products' costs from elsewhere and you can infer approximate ranges for the rest.
The same happens with cost_rate or supplier_id. Sort by supplier ID and you learn which products share a supplier.
The fix is to enumerate with the in: rule.
'sort' => ['sometimes', 'in:id,name,price,created_at'],
The chapter 7 stance of "enumerate what you publish" applies not only to the contents of the response but to the sort keys as well.
How do you respond to the argument that preventLazyLoading should be enabled in production too?
Sample answer
Disagree. But the concern behind the argument is valid, so it has to be answered.
Consider what happens if you enable it. One forgotten with() and that endpoint throws and returns a 500. Without it enabled, it is merely slow and returns the correct result.
From the user's point of view, "a bit slow" and "does not work" are different degrees of harm. With the first you wait and get your result; with the second the feature is unusable. A missing load is a performance problem, not a correctness problem. Enabling it promotes a performance problem into an availability problem.
Behind the argument, though, is surely the worry of "it reaching production without anyone noticing during development." Answer that by other means.
- Enable
preventLazyLoadinglocally and in the test environment. A failing test in CI catches it before merge - Always write the Feature tests from chapter 10 for endpoints that return a listing
- Log slow queries in production and alert when a threshold is crossed
The combination "strict in development, lenient in production" appears in other Laravel settings too. APP_DEBUG has the same shape. The judgment is that if you can vary strictness by environment, you should.