Laravel pagination for custom queries

If you have used laravel for any CRUD application you may used the paginate method for generating pagination links. In case you haven’t below is how it works.

Say you have a table posts then SELECT query for selecting fifteen posts would be as below

$posts = DB::table('posts')->paginate(15);//15 is the number of resultset per page

The above code will automatically take care of offsets as per the page being displayed. So say if you are on page 2 then this query will automatically return resultsets with LIMIT 15,15 i.e row 15 to row 30

To display pagination links you would use the below code

$posts->links()

You would be surprised to know that above code will generate below markup for pagination. If you have a close look at it you’ll realize that it is actually the pagination markup of Bootstrap. Laravel does this out of the box.

«
1
2
»

http://localhost/dashboard is the url of our application. ?page=2 is appended by laravel to generate pagination. The paginate() method retrieves the resultset with reference to value of $_REQUEST['page'].

Limitation of pagination in groupBy

Unfortunately Laravel pagination doesn’t work well with groupBy queries. For this you’ll have to generate pagination links manually. Even for complex queries using DB::select you will face the same issue. So in such cases Laravel allows you to create the pagination instance manually. Below is how it is done

$all_transactions = DB::select(DB::raw($query)); //$query is raw SQL query say SELECT * FROM a LEFT JOIN b on a.id = b.a_id GROUP BY a.id
$pagination = Paginator::make($all_transactions, count($all_transactions), $results_per_page);

To generate the pagination markup in your view file you just need to write the below code

$pagination->links()

Appending query strings to pagination links

Many times you may want to conditionally append query string to your paginated links. Say you want to append user_id=2 to get user with ID 2. So instead of only http://localhost/dashboard?page=2 you want it http://localhost/dashboard?page=2&user_id=2. Well, not a big deal for Laravel. You can anytime do that using the appends method on Paginator

$pagination->appends(array('user_id'=>2))->links()

And since the input parameter to appends is an array you may append more than one parameter

$pagination->appends(array('user_id'=>2,'department_id'=>5))->links()