How to Use CTE in Yii2 GridView: ArrayDataProvider vs. SQL View Approaches

What You Will Learn

• Definition and benefits of a CTE (Common Table Expression)
• Two practical ways to integrate CTE-based queries into Yii2 GridView:
  1. Using ArrayDataProvider
  2. Creating a database view for use with ActiveDataProvider (supports built-in filtering)

Introduction: What Is a CTE?

A Common Table Expression (CTE) is a SQL construct that allows you to define a temporary result set, which can then be referenced within a larger SELECT, INSERT, UPDATE, or DELETE statement. For example:

WITH ranked_users AS (
    SELECT
        id,
        name,
        RANK() OVER (ORDER BY points DESC) AS rank
    FROM user
)
SELECT *
FROM ranked_users
WHERE rank <= 10;

Out of the box, Yii2’s ActiveRecord does not support writing CTEs inline. However, two straightforward workarounds make it possible to leverage CTE logic inside a Yii2 GridView:

1. Execute the CTE query directly and feed results into an ArrayDataProvider (no filter UI support).
2. Create a database view that wraps the CTE SQL, then define an ActiveRecord model for that view and use ActiveDataProvider (grid filters and sorting work natively).

Choose the method that best fits your requirement: quick display versus fully integrated grid filtering.

Method 1: Using ArrayDataProvider for CTE Queries

This approach is ideal if:

• You only need to show the result without requiring Yii2 GridView filter inputs.
• You are comfortable handling any sorting or pagination manually (using the provider’s basic configuration).

Step 1: Write and Execute the CTE SQL

In your controller action (e.g., actionIndex):

use Yii;
use yii\data\ArrayDataProvider;
public function actionIndex()
{
    $sql = <<<SQL
WITH ranked_users AS (
    SELECT
        id,
        name,
        RANK() OVER (ORDER BY points DESC) AS rank
    FROM user
)
SELECT *
FROM ranked_users
WHERE rank <= 10;
SQL;
    // Execute the query directly using DAO
    $rows = Yii::$app->db->createCommand($sql)->queryAll();
    // Create an ArrayDataProvider
    $dataProvider = new ArrayDataProvider([
        'allModels' => $rows,
        'pagination' => [
            'pageSize' => 10,
        ],
        'sort' => [
            'attributes' => ['id', 'name', 'rank'],
        ],
    ]);
    return $this->render('index', [
        'dataProvider' => $dataProvider,
    ]);
}

Step 2: Render the GridView in the View

In views/your-controller/index.php:

use yii\grid\GridView;
use yii\helpers\Html;
/* @var $dataProvider yii\data\ArrayDataProvider */
?>
<div class="ranked-users-index">
    <h1>Top Ranked Users</h1>
    <?= GridView::widget([
        'dataProvider' => $dataProvider,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
            'id',
            'name',
            'rank',
        ],
    ]); ?>
</div>

Pros and Cons

• Pros
  • Direct control over any SQL, including advanced CTE and window functions.
    • Simple to implement when you just need to display a fixed set of results.
• Cons
  • GridView filtering (the filter inputs above each column) is not available. If you need filter forms, you must build them manually and adjust the query.
  • Sorting must rely on the provider’s basic sort configuration; it won’t automatically adjust the CTE unless you rebuild the SQL based on GET parameters.

Method 2: Create a Database View and Use ActiveDataProvider

This approach integrates smoothly with the Yii2 filtering UI. By wrapping the CTE in a database view, you can treat the view exactly like a regular table in ActiveRecord. The GridView will support filtering, sorting, and pagination out of the box.

Step 1: Create a SQL View

Run the following SQL in your database (MySQL, PostgreSQL, etc.). Adjust column names and table names to match your schema.

CREATE OR REPLACE VIEW top_users AS
WITH ranked_users AS (
    SELECT
        id,
        name,
        RANK() OVER (ORDER BY points DESC) AS rank
    FROM user
)
SELECT *
FROM ranked_users
WHERE rank <= 10;

• top_users is the view name.
• The CTE logic remains inside the WITH clause.
• Final SELECT returns only rows with rank <= 10.

Step 2: Generate (or Manually Create) an ActiveRecord Model for the View

You can use Gii or write it by hand. If you run Gii, point it to the top_users view; it will generate a TopUser model class. Example of a manual definition:

namespace app\models;
use yii\db\ActiveRecord;
class TopUser extends ActiveRecord
{
    /**
     * Return the name of the view (same as tableName for tables).
     */
    public static function tableName()
    {
        return 'top_users';
    }
    /**
     * Define validation rules (mark all attributes as safe for search).
     */
    public function rules()
    {
        return [
            [['id', 'name', 'rank'], 'safe'],
        ];
    }
    /**
     * Optional: Define labels if you want to customize column headers
     */
    public function attributeLabels()
    {
        return [
            'id'   => 'User ID',
            'name' => 'User Name',
            'rank' => 'Rank',
        ];
    }
}

Step 3: Configure ActiveDataProvider in Controller

In your controller’s action (for example, actionTopUsers):

use app\models\TopUser;
use yii\data\ActiveDataProvider;
public function actionTopUsers()
{
    $query = TopUser::find();
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'pagination' => [
            'pageSize' => 10,
        ],
        'sort' => [
            'defaultOrder' => ['rank' => SORT_ASC],
            'attributes' => ['id', 'name', 'rank'],
        ],
    ]);
    return $this->render('top-users', [
        'dataProvider' => $dataProvider,
    ]);
}

If you also want search/filter capabilities, you can create a TopUserSearch model (extending TopUser) with filtering rules, then pass both $searchModel and $dataProvider to the view. This follows the standard Yii2 “index with search model” pattern.

Step 4: Render the GridView in the View

In views/your-controller/top-users.php:

use yii\grid\GridView;
use yii\helpers\Html;
/* @var $dataProvider yii\data\ActiveDataProvider */
/* @var $searchModel app\models\TopUserSearch */ // if using a search model
?>
<div class="top-users-index">
    <h1>Top Ranked Users</h1>
    <?= GridView::widget([
        'dataProvider' => $dataProvider,
        'filterModel'  => isset($searchModel) ? $searchModel : null,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
            'id',
            'name',
            'rank',
            ['class' => 'yii\grid\ActionColumn'],
        ],
    ]); ?>
</div>

• If you generated TopUserSearch, pass it as filterModel so that you get filter fields above each column automatically.
• The built-in ActionColumn will render view, update, delete links if corresponding controller actions exist (you can remove it if not needed).

Pros and Cons

• Pros
  • The GridView’s filtering, sorting, and pagination all work without extra manual code.
  • Everything feels native to Yii2: you can use Gii, code completion, search models, and so on.
  • If you need to change the sorting/filter logic, you can override search() or adjust the view SQL in one place.
• Cons
  • Requires database-level privileges to create or alter views.
  • For highly dynamic CTEs (where parameters or logic change at runtime), you might need to recreate or modify the view frequently.
  • Adding parameters to a view (for example, filtering by date range inside the CTE) often involves creating multiple views or using dynamic SQL outside the view.

Comparison of Approaches