Pagination of GROUPED query results within Laravel 4.2
I have a query which currently returns in the region of 25k records, with this likely to exceed 100k by the end of year. Obviously this is paginated for readability.....
Having delved into the code for pagination, it appears that the standard Laravel 4.2 code returns the entire dataset in order to get the TOTAL count, and then slices the resulting collection in order to return the paginated result.
My question is, can anyone highlight any reasons or circumstances under which the following would not be more efficient or indeed cause issues?
\Illuminate\Database\Eloquent\Builder.php Lines 259 -
protected function groupedPaginate($paginator, $perPage, $columns)
{
$total_records = \DB::select('select count(*) as total from ('.$this->toSql().') as records', $this->getBindings())[0];
$total = $total_records->total;
$page = $paginator->getCurrentPage($total);
$this->query->forPage($page, $perPage);
return $paginator->make($this->get($columns)->all(), $total, $perPage);
The purpose here is to nest the original GROUPED SQL query within a single select, and retrieve a single row with the total record count. Once you have this total, then retrieve a limited set of results, as you would with the standard pagination process.
Like I said, any advice as to why this wouldn't be more efficient or indeed fail, would be great!
And again for clarity, this is running on Laravel 4.2.
Please or to participate in this conversation.