Yii 2.0 Pagination

February 15, 2015 by Hirad Daneshvar

In this post I want to show you how to use pagination when your are fetching a large amount of data from your database. We all know that showing our user tons of data is unpleasant for most of them and cause less site views. As a result, we may notice that we have lost a considerable amount of reader within months. So, what should we do? Thanks to pagination we can overcome this fear of ours.

In this post I want to show you how to use pagination when your are fetching a large amount of data from your database. We all know that showing our user tons of data is unpleasant for most of them and cause less site views. As a result, we may notice that we have lost a considerable amount of reader within months. So, what should we do? Thanks to pagination we can overcome this fear of ours.

What is "Pagination"? When we are querying our database for a considerable amount of data, instead of fetching all of our data, we can simply limit the number of fetched records using 'LIMIT' in our SQL syntax. Some of you may ask, what happens if we want to see the rest of the results. Well don't worry, we will get to that matter, shortly. So when we use 'LIMIT' in our query, we are basically telling our database that we want  restricted number of records.

As an example, we have one table which is called ‘Person‘. ‘Person‘ attributes are, ‘pid, name, type‘ and our primary key is ‘pid‘. We want to fetch only two records of 'Person'. So instead of writing the following SQL query

"SELECT * FROM Person"

we can write the following SQL query

"SELECT * From Person LIMIT 2"

Now, if we want to get the rest of the records , we can use OFFSET in our SQL query. Lets take the following SQL query as an example:

"SELECT * From Person LIMIT 2 OFFSET 2"

The query above, returns 2 records from 'Person' like our previous query, but instead of fetching the first two records, it gives us the second two. Imagine our 'Person' table is like below:

Our first query using LIMIT returns the following records:

However our second query using both LIMIT and OFFSET returns the following records:

When we want to paginate our results we have to dynamically change OFFSET value. For example on the first page our OFFSET is 0, on the second page it should dynamically change to 2 and so on. I will talk about this in another post.

So, we can handle this using Pagination library. We have to include this library in the beginning of our controller, which we want to use this query. So we have to include the following library: yii\data\Pagination

You know how to fetch all records in 'Person'. For the query we talked about we have to use the following Yii 2.0 query:

$query             = \app\models\Person::find();
$countQuery        = clone $query;
$pages             = new Pagination([
     'totalCount' => $countQuery->count(), 'pageSize' => 2]);
$fetch_person = $query->offset($pages->offset)
    ->limit($pages->limit)
    ->all();

The code above dynamically paginates our data fetching query. $query is our original query, you can add orderBy, groupBy, where, joinWith, and etc. to it. $countQuery clones our original query to use for pagination. $pages, uses Pagination class (library) and $countQuery to both count the number of data we have without pagination and using pageSize we are limiting the number of records. So, pageSize is our SQL LIMIT value. $fetch_person uses $query and offset to tell our database from which record we want to fetch data. As you can see, after offset we have used limit so that the database knows how many records we want to fetch. $pages->limit is set based on pageSize. On each page, $page->offset changes dynamically so the OFFSET value of our SQL query is changing dynamically. We have to pass $fetch_person and $pages to our view so that we can print our results and the pager itself.

In our view we only have the desired number of records. To print the pager we have to use the following code wherever we want to print our pager:

<?php
    echo yii\widgets\LinkPager::widget([
        'pagination' => $pages,
    ]);
?>

Now you have paginated your results.