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:

DB::with('relation')->groupBy('something')->paginate(50);

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);
$collection->load('relation');

// 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);
$results->load('relation');

Unfortunately this is not possible, yet.


Comments

Nicolas Danleon 10 months ago

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


sirajul1991 1 year ago

I get the load method working.


Blueowner 2 years ago

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

Thanks for sharing.