Introduction

Pagination and sorting are essential features for handling large datasets in Yii2. Yii2 provides built-in support for both using yii\data\Pagination and yii\data\Sort, which work seamlessly with ActiveDataProvider and ArrayDataProvider.

This tutorial covers:
How pagination works in Yii2
Implementing sorting for GridView & ListView
Customizing pagination and sorting

1. Understanding Pagination in Yii2

Pagination breaks large datasets into smaller pages, improving performance and usability. Yii2 provides automatic pagination when using ActiveDataProvider or ArrayDataProvider.

Add Bootstrap4 or 5 Pagination in Config (Optional)

//web.php or main.php
 'container' => [
        'definitions' => [
            \yii\widgets\LinkPager::class => \yii\bootstrap5\LinkPager::class,
            'yii\bootstrap5\LinkPager' => [
                'firstPageLabel' => 'First',
                'lastPageLabel' => 'Last',
                'options' => ['class' => 'pagination pagination-primary mg-sm-b-0']
            ],
        ],
    ],

Basic Pagination Example

use yii\data\Pagination;
use app\models\Post;

$query = Post::find();
$countQuery = clone $query;
$totalCount = $countQuery->count();

$pagination = new Pagination([
    'totalCount' => $totalCount,
    'pageSize' => 10, // Number of records per page
]);

$posts = $query->offset($pagination->offset)
               ->limit($pagination->limit)
               ->all();

Here, $pagination->offset and $pagination->limit ensure only a subset of records is fetched from the database.

2. Displaying Pagination in Views

To display pagination controls, use yii\widgets\LinkPager in your view file:

use yii\widgets\LinkPager;

foreach ($posts as $post) {
    echo "<h3>{$post->title}</h3>";
    echo "<p>{$post->content}</p>";
}

// Display pagination controls
echo LinkPager::widget([
    'pagination' => $pagination,
]);

Customizing LinkPager

You can customize the pagination UI:

echo LinkPager::widget([
    'pagination' => $pagination,
    'prevPageLabel' => 'Previous',
    'nextPageLabel' => 'Next',
    'firstPageLabel' => 'First',
    'lastPageLabel' => 'Last',
    'maxButtonCount' => 5,
    'options' => ['class' => 'pagination'],
]);

3. Pagination in GridView & ListView

Yii2's ActiveDataProvider automatically applies pagination in GridView and ListView.

Pagination in GridView

use yii\data\ActiveDataProvider;
use app\models\Post;
use yii\grid\GridView;

$dataProvider = new ActiveDataProvider([
    'query' => Post::find(),
    'pagination' => [
        'pageSize' => 5,
    ],
]);

echo GridView::widget([
    'dataProvider' => $dataProvider,
]);

Pagination controls will appear automatically.

Pagination in ListView

use yii\widgets\ListView;

echo ListView::widget([
    'dataProvider' => $dataProvider,
    'itemView' => '_post_item',
    'pager' => [
        'class' => 'yii\widgets\LinkPager',
        'maxButtonCount' => 5,
    ],
]);

Here, _post_item.php is the partial view that renders each record.

4. Implementing Sorting in Yii2

Sorting allows users to order records by clicking on column headers.

Basic Sorting Example

use yii\data\Sort;

$sort = new Sort([
    'attributes' => [
        'title',
        'created_at' => [
            'asc' => ['created_at' => SORT_ASC],
            'desc' => ['created_at' => SORT_DESC],
            'default' => SORT_DESC,
            'label' => 'Date',
        ],
    ],
]);

$posts = Post::find()->orderBy($sort->orders)->all();

The user can now sort posts by title or created_at.

5. Sorting in GridView & ListView

Sorting in GridView

GridView automatically enables sorting when using ActiveDataProvider:

$dataProvider = new ActiveDataProvider([
    'query' => Post::find(),
    'sort' => [
        'attributes' => ['title', 'created_at'],
    ],
]);

echo GridView::widget([
    'dataProvider' => $dataProvider,
]);

Users can now click on column headers to sort records.

Sorting in ListView

use yii\widgets\ListView;

echo ListView::widget([
    'dataProvider' => $dataProvider,
    'sorter' => [
        'attributes' => ['title', 'created_at'],
    ],
]);

A sorting dropdown appears above the list.

6. Customizing Pagination & Sorting in URL

Pagination Parameters in URL

By default, Yii2 appends ?page=2 to the URL for pagination. You can customize this:

'pagination' => [
    'pageParam' => 'p', // Changes ?page=2 to ?p=2
],

Sorting Parameters in URL

By default, Yii2 uses ?sort=title. You can modify it:

'sort' => [
    'sortParam' => 'order', // Changes ?sort=title to ?order=title
],

7. Infinite Scrolling with Pagination

To implement infinite scrolling, use jScroll or JavaScript with ListView.
Modify ListView like this:

echo ListView::widget([
    'dataProvider' => $dataProvider,
    'itemView' => '_post_item',
    'pager' => ['class' => \kop\y2sp\ScrollPager::class],
]);

Install yii2-scroll-pager with:

composer require kop/yii2-scroll-pager

Advanced Sorting Methods in Yii2

Yii2 allows complex sorting using raw SQL expressions, CASE conditions, and multiple field priorities. This section covers advanced sorting techniques that go beyond default attribute-based sorting.

1. Sorting with Multiple Priorities

You can prioritize sorting conditions by defining a custom orderBy clause.

Example: Sorting by Featured Items First

$query->orderBy(new \yii\db\Expression('featured DESC, created_at DESC'));

• featured DESC ensures that featured items appear first.
• created_at DESC sorts remaining items by the latest first.

2. Sorting with CASE Statements

When sorting needs to be dynamic based on different conditions, use CASE in SQL.

Example: Conditional Sorting for Different Types

$query->orderBy(new \yii\db\Expression('pined_safari DESC, (CASE 
    WHEN type=1 THEN estimate_price_min 
    WHEN type=2 THEN cost_per_person 
END) ASC'));

• pined_safari DESC: Pins certain records at the top.
• CASE checks type and sorts:
  • If type=1, it sorts by estimate_price_min.
  • If type=2, it sorts by cost_per_person.

3. Sorting Using Custom Expressions

You can use Yii2’s Expression class to define custom sorting logic.

Example: Sorting by Name Length

$query->orderBy(new \yii\db\Expression('LENGTH(name) ASC, name ASC'));

• First, it sorts by the length of the name field (shorter names appear first).
• Then, it applies alphabetical sorting.

4. Sorting by a Calculated Field

Sometimes, sorting must be based on dynamically calculated values.

Example: Sorting by Discount Percentage

$query->orderBy(new \yii\db\Expression('((original_price - discount_price) / original_price) DESC'));

• This calculates the discount percentage and sorts in descending order.

5. Sorting by Custom Function Results

If sorting requires complex logic, consider using a computed column or a custom function.

Example: Sorting by Ratings with a Fallback

$query->orderBy(new \yii\db\Expression('COALESCE(rating, 0) DESC, reviews_count DESC'));

• COALESCE(rating, 0) DESC: Ensures null ratings are treated as 0.
• reviews_count DESC: Prioritizes items with more reviews.

Using MySQL FIELD() for Custom Sorting in Yii2

The MySQL FIELD() function is useful when sorting records based on a predefined list of values rather than a standard ascending or descending order. This allows custom ordering that isn't possible with standard ORDER BY clauses.

1. Sorting by a Predefined Order Using FIELD()

The FIELD() function assigns a sorting priority based on the given values.

Example: Sorting by Specific Status Order

Imagine you have a list of orders with different statuses, and you want them sorted in a specific order (Pending, Processing, Completed, Cancelled).

$query->orderBy(new \yii\db\Expression("FIELD(status, 'Pending', 'Processing', 'Completed', 'Cancelled')"));

• FIELD(status, 'Pending', 'Processing', 'Completed', 'Cancelled'): Orders results in the specified sequence.
• Without FIELD(), MySQL would sort statuses alphabetically.

2. Sorting by Priority Based on Custom List

Example: Sorting Users by Role Priority

If you have different user roles (Admin, Manager, Employee, Intern), you might want to sort them by role hierarchy.

$query->orderBy(new \yii\db\Expression("FIELD(role, 'Admin', 'Manager', 'Employee', 'Intern')"));

• Admin appears first, followed by Manager, then Employee, and finally Intern.

3. Combining FIELD() with Other Sorting Conditions

You can mix FIELD() with other sorting conditions.

Example: Sorting Products by Priority and Price

$query->orderBy(new \yii\db\Expression("
    FIELD(category, 'Electronics', 'Clothing', 'Books', 'Furniture') ASC,
    price ASC
"));

• First, Electronics appears at the top, followed by Clothing, then Books, and finally Furniture.
• Then, within each category, products are sorted by price in ascending order.

4. Sorting with FIELD() and a Dynamic List

Sometimes, the sorting order is dynamic and stored in an array.

Example: Dynamic Sorting Order from PHP Array

$sortOrder = ['Gold', 'Silver', 'Bronze'];
$orderString = "'" . implode("','", $sortOrder) . "'"; 

$query->orderBy(new \yii\db\Expression("FIELD(membership_type, $orderString)"));

• Converts ['Gold', 'Silver', 'Bronze'] into 'Gold','Silver','Bronze'.
• Ensures results follow this exact order.