Laravel

Laravel Custom Pagination using Raw Query/QueryBuilder

February 23, 2018

author:

Laravel Custom Pagination using Raw Query/QueryBuilder

For most of the developers, Pagination is a headache. But with Laravel, pagination can be achieved with an inbuilt method called paginate(). It configures limits and offset automatically.

# Basic Laravel Pagination

Before setting on our goal to work with custom pagination with QueryBuilder, let us first have a strong base on Pagination in Laravel. As discussed earlier, Laravel uses paginate() method to Paginate the records.

Here are some quick instances,

Pagination using Query Builder:

$posts= DB::table('posts')->paginate(10);

Pagination using Eloquent ORM:

$posts= App\Post::paginate(10);

As we know, here 10 represents the limit, i.e., number of records per page. You can set that to whatever is required.

# Custom Pagination Overview

For some complex use cases, we may need to manually configure pagination in a Laravel application. We can achieve by using either an Illuminate\Pagination\Paginator or Illuminate\Pagination\LengthAwarePaginator instance.

Both of them work similarly except that LengthAwarePaginator requires knowing the result set (or records) count while Paginator doesn’t. Also, we need to slice an array of a result set to these instances to achieve expected formatting. We will LengthAwarePaginator for this post shortly.

# Laravel Custom Pagination

Enough of talking let us now straightly move towards the custom pagination. Here are the steps:

1. Manually Custom Query the database:
Using Query Builder, we are connecting to MySQL data and fetching the categories from categories table manually

$raw_query = \DB::connection('mysql')->table('categories')
 ->addSelect('categories.*');

2. Collection Count:
Since we want to use instance of LengthAwarePaginator , let us count the total result sets:

$totalCount = $raw_query->count();

3. Define Page:
Note that though we are customizing pagination to suit our needs, it is recommended to let it function as conventional does under the hood. That is, Laravel detects the current and other pages with the help of page query string argument. It is passed with HTTP Request and is automatically detected by Laravel. Also, paginate() method, and other custom instances generate this argument automatically. Hence all we need to do fetch this page input from Request.

$page = $request->input('page') ?:1;

Here we are fetching page input value, and also defaulting it to be 1 in case of no value is passed (viz. a case for the first page)

4. A condition for Pagination:
Here’s the real logic of pagination. We are checking for $page value and if it exists, it means that it is not the first page. $skip stores the offset value and we store fresh records in $raw_query.

$page = $request->input('page') ?:1;
if ($page) {
    $skip = 10 * ($page - 1);
    $raw_query = $raw_query->take(10)->skip($skip);
} else {
    $raw_query = $raw_query->take(10)->skip(0);
}

5. Fetch Query String:
We need to append details of the result sets, page number, etc. to the URL, so let us fetch that:

$parameters = $request->getQueryString();

6. Replace Page:
To keep the pages rolling, we need to replace the $page value each time for new page.

$parameters = preg_replace('/&page(=[^&]*)?|^page(=[^&]*)?&?/','', $parameters);

7. Next Page URL:
Since we are creating custom pagination, page URLs must be configured by us. We will append the next page path:

$path = url('/') . '/categories?' . $parameters;

8. Convert collection to array:
Since we need to pass an array to the LengthAwarePaginator() instance, let us convert the database record collection to array:

$categories = $raw_query1->get()->toArray();

9. Create Pagination:
And finally the moment, you’ve been waiting for! Configure Pagination with LengthAwarePaginator().

$paginator = new \Illuminate\Pagination\LengthAwarePaginator($categories, $totalCount, 10, $page);

Here we are resulting dataset array, the total count of the dataset, offset, and $page value respectively.

10. Pass paginator to view:
And finally pass the paginated dataset to a view to display it to the users.

$paginator = $paginator->withPath($path);

Here we are also attaching the URI path for next page.

11. Usage in View:
You can pass $paginator from the previous step to the required blade view and use it with blade syntax. For instance,

{{ $paginator->links() }}

And that’s it. Here’s how we created a customized Laravel Pagination in 11 simple steps. Hope you followed along. Below is the whole code snippet for reference:

$raw_query = \DB::connection('mysql')->table('categories')
    ->addSelect('categories.*');
$totalCount = $raw_query->count();

$page = $request->input('page') ?:1;
if ($page) {
    $skip = 10 * ($page - 1);
    $raw_query = $raw_query->take(10)->skip($skip);
} else {
    $raw_query = $raw_query->take(10)->skip(0);
}

$parameters = $request->getQueryString();
$parameters = preg_replace('/&page(=[^&]*)?|^page(=[^&]*)?&?/','', $parameters);
$path = url('/') . '/categories?' . $parameters;

$categories = $raw_query->get()->toArray();

$paginator = new \Illuminate\Pagination\LengthAwarePaginator($categories, $totalCount, 10, $page);
$paginator = $paginator->withPath($path);

Conclusion:

This was an interesting post where we learned to work with Laravel Pagination using Query Builder from scratch. We first build a good reference point by discussing about basic pagination flow in Laravel. We then shifted to custom pagination and scrutinized each step for crystal clear understanding which can be very helpful for building complex stuff.

QUESTIONS & COMMENTS:

Thank you for reading. If you suggestions and questions, share them in the comment section below.

Leave a comment

Your email address will not be published. Required fields are marked *