tero.co.uk

Laravel Query Builder with Paging

Pagination in a database is performed by adding a LIMIT and OFFSET to the SQL query. So if you want the 3rd page of results and each page has 10 results, you would add LIMIT 10 OFFSET 20 to your query. But you also need to efficiently figure out the total number of results (so you know how many page numbers to show).

There are two approaches to this. You can run the query again without the LIMIT and with COUNT(*), or you can use MySQL's built in SQL_CALC_FOUND_ROWS and then call FOUND_ROWS() immediately afterwards. Laravel uses the COUNT(*) method. There is debate over which method is faster, but the main problem with COUNT(*) is that it doesn't work on queries which already have a GROUP BY.

I needed to do paging of queries with a GROUP BY, so I wanted to tell Laravel to use the SQL_CALC_FOUND_ROWS instead of COUNT(*). This turned out to be quite involved but I think I managed it. It involves extending the Builder class to implement a new addPagination method and override getModels to inject the SQL_CALC_FOUND_ROWS.

This has only been tested in a few situations but hopefully someone will find it useful or be able to adjust it to their needs. This was was added 21/7/2017.

Download

First download the BuilderWithPagination PHP file and save it somewhere in your project.

Tell the Model to Use the New Class

Each model which should use this new class needs to include the file:

use path\to\BuilderWithPagination;

And it needs to add this method to enable it:

public function newEloquentBuilder($query)
{
    return new BuilderWithPagination($query);
}

Implement Paging

To implement paging, call the addPagination method, passing in the number of results per page, and the page number (starting from 1). Then get() the results, and use the count() method to get the total number of results. For example, assuming a model called Sale:

$query = Sale::addPagination(10,1); //for 1st page of 10 results
$salesdata = $query->get(); //get the data
$numTotalResults = $query->count(); //total number of results without paging