Skip to content

Instantly share code, notes, and snippets.

@crishoj
Created May 17, 2017 09:15
Show Gist options
  • Save crishoj/fea0a2560ae1efca1e99b24efae83ae2 to your computer and use it in GitHub Desktop.
Save crishoj/fea0a2560ae1efca1e99b24efae83ae2 to your computer and use it in GitHub Desktop.
Paginate Eloquent query using SQL_CALC_FOUND_ROWS and FOUND_ROWS() — avoids running the query twice
<?php
function paginateOptimized(Builder $builder, $perPage = null, $pageName = 'page', $page = null): LengthAwarePaginator
{
$model = $builder->getModel();
$page = $page ?: Paginator::resolveCurrentPage($pageName);
$perPage = $perPage ?: $model->getPerPage();
$sql = $builder->forPage($page, $perPage)->toSql();
if (!starts_with($sql, 'select ')) {
throw new \Exception('SELECT statement expected to start with "select "');
}
// Insert SQL_CALC_FOUND_ROWS option
$sql = substr_replace($sql, ' sql_calc_found_rows', 6, 0);
$rows = DB::select($sql, $builder->getBindings());
// Get the calculated total count using FOUND_ROWS()
$total = DB::selectOne('select found_rows() as total')->total;
$models = $model->hydrate($rows)->all();
if (count($models) > 0) {
$models = $builder->eagerLoadRelations($models);
}
$models = $model->newCollection($models);
return new LengthAwarePaginator($models, $total, $perPage, $page, [
'pageName' => $pageName,
'path' => Paginator::resolveCurrentPath(),
]);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment