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.

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.