ArrayDataProviderActiveDataProvider (supports built-in filtering)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:
ArrayDataProvider (no filter UI support).ActiveDataProvider (grid filters and sorting work natively).Choose the method that best fits your requirement: quick display versus fully integrated grid filtering.
This approach is ideal if:
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,
]);
}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>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.
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.WITH clause.SELECT returns only rows with rank <= 10.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',
];
}
}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.
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>TopUserSearch, pass it as filterModel so that you get filter fields above each column automatically.ActionColumn will render view, update, delete links if corresponding controller actions exist (you can remove it if not needed).search() or adjust the view SQL in one place.| Approach | Filtering Support | Best Use Case | Complexity |
|---|---|---|---|
| ArrayDataProvider | No | Quick, one-off display of CTE results | Low |
| SQL View + ActiveProvider | Yes | Fully functional grid with filters/sorts | Moderate |
CREATE OR REPLACE VIEW … again.TopUserSearch class via Gii.search(), you might add conditions like andFilterWhere(['like', 'name', $this->name]).rules() in your model mark attributes as safe, otherwise filter inputs will be ignored.WITH clauses chained).Using CTEs in Yii2 GridView unlocks powerful SQL capabilities—ranking, recursive queries, multi-step transformations—while still fitting within the MVC workflow. Depending on your needs:
Both techniques let you write efficient, maintainable code while leveraging the full power of SQL. Pick the one that aligns with your project’s requirements and database permissions.
Sign in to join the discussion and post comments.
Sign in