Jens Segers on Sep 22 2014

Laravel pagination with grouping and eager loading

I found myself in a bit of a strange situation when I was doing this query in Laravel:


I was using this query to display a large amount of records in a paginated table. Strangely, this page was loading really slow, and when looking at the query log it became obvious why.

Laravel's query builder has different a pagination logic for grouped and ungrouped queries. When using grouping it will get all the records, and manually slice it based on the current page and number of items to show per page. While without grouping, it will add a simple limit statement to your query.

Because I was using grouping, this resulted in all of my records being fetched, and the relations being eager loaded for every single record, even if it was on the next page.

The reason behind this logic difference, is that the paginator needs to know the total number of records to determine how many pages there are. And when using grouping, the query builder can't simply add count(1) to the select statement.

My solution for this was to fetch all the records, and then slice the results before eager loading the relations.

// Get all records.
$results = DB::groupBy('something');

// Get pagination information and slice the results.
$total = count($results);
$start = (Paginator::getCurrentPage() - 1) * $perPage;
$sliced = array_slice($results, $start, $perPage);

// Eager load the relation.
$collection = Model::hydrate($sliced);

// Create a paginator instance.
return Paginator::make($collection->all(), $total, $perPage);

It's not really the prettiest code, but it's a big improvement as it is loading a lot less relations.

It would be even better if we could eager load relations on the paginator instance. So that this would become possible:

$results = DB::groupBy('something')->paginate(50);

Unfortunately this is not possible, yet.


Blueowner 4 years ago

Amazing! Didn't know about the ->hydrate() method.

Thanks for sharing.

sirajul1991 4 years ago

I get the load method working.

Nicolas Danleon 3 years ago

Oh but it is posible now, in 2016!! Greetings from the future my friend!

s4p3r 1 year ago

Coming to see the past, very great

Ayman Elarian 1 year ago

its not a correct way to handle pagination ! if you have 1,000,000 , why load them all and let php count them at every page !

olidev 1 year ago

Pagination in Laravel is best done with VueJS on the front end. The result is a seamless browsing of website. This means each time you click on a next page, the page will not load but the content of next page will be displayed without loading. This is the power of pagination in vue( ).