Yii 2.0: Filter & Sort by Summary Data in GridView Yii 2.0


This wiki explains how to add summary data from related models into your Yii Framework 2.0 gridview with filtering and sorting by the summary columns.

Note: This is a variation to this wiki which filters and sorts by related fields. In this wiki, we will see specifically how to pull in SUMMARY data from related tables.

Example Structure

Let's say you have the following tables for Customer and Order. A customer can have multiple orders. Your objective is to display a gridview for the Customer with an order amount summary from Order table. You should be able to also sort and filter/search by order amount.

/* Customers */
CREATE TABLE `tbl_customer` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique customer identifier',
    `name` VARCHAR(150) NOT NULL COMMENT 'Customer name',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Order master table';
/* Orders */
CREATE TABLE `tbl_order` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique order identifier',
    `created_on` DATE NOT NULL COMMENT 'Order creation date',
    `details` VARCHAR(200) COMMENT 'Order Details',
    `amount` DECIMAL(5,2) NOT NULL COMMENT 'Order Amount',
    `customer_id` INT(11) COMMENT 'Related customer identifier',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Order transactions table';
/* Foreign Key */
ALTER TABLE `tbl_order`
ADD CONSTRAINT `tbl_order_FK1` 
FOREIGN KEY (`customer_id`) 
REFERENCES `tbl_customer` (`id`) 
, ADD INDEX `tbl_order_FK1` (`customer_id` ASC);


Generate your models and CRUD via Gii. You should now have the following model classes generated:

  1. Customer: The base model for tbl_person
  2. CustomerSearch: The search and filtering model for Customer within gridview.
  3. Order: The base model for tbl_order.
  4. OrderSearch: The search and filtering model for Order within gridview.

Gridview Scenarios

Let's consider the following scenarios, that you want to display in the GridView within the index view generated for Customer.

Scenario 1: Display Order Amount Summary for each customer (and allow filter and sort)

An example describing how to add a OrderAmount column within the Customer grid with sorting and filtering. This will be a summary of amount field from tbl_order for each customer_id.

Scenario 1 Steps

STEP 1: Implement a stat relation to get summary for OrderAmount.

Setup base model

 * Order amount for customer 
public function getOrderAmount()
    return $this->hasMany(Order::className(), ['customer_id' => 'id'])->sum('amount');
/* Your model attribute labels */
public function attributeLabels() {
    return [
        /* Your other attribute labels */
        'orderAmount' => Yii::t('app', 'Order Amount')

STEP 2: Add an attribute orderAmount to your model CustomerSearch and configure your rules.

Setup search model

/* your calculated attribute */
public $orderAmount;
/* setup rules */
public function rules() {
   return [
    /* your other rules */
    [['orderAmount'], 'safe']
 * setup search function for filtering and sorting 
 * based on `orderAmount` field
public function search($params) {
    $query = Customer::find();
    $subQuery = Order::find()
        ->select('customer_id, SUM(amount) as order_amount')
    $query->leftJoin(['orderSum' => $subQuery], 'orderSum.customer_id = id');
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
     * Setup your sorting attributes
     * Note: This is setup before the $this->load($params) 
     * statement below
        'attributes' => [
            'orderAmount' => [
                'asc' => ['orderSum.order_amount' => SORT_ASC],
                'desc' => ['orderSum.order_amount' => SORT_DESC],
                'label' => 'Order Name'
    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
        'id' => $this->id,
    $query->andFilterWhere(['like', 'name', $this->name]);
    // filter by order amount
    $query->andWhere(['orderSum.order_amount' => $this->orderAmount]);
    return $dataProvider;

STEP 3: Configure your gridview columns in your view index file

Setup view file

echo GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        ['class' => 'yii\grid\ActionColumn'],

Voila, yes now, your orderAmount column as well in the grid view should be available for sort and filtering.

Potential Improvements

  1. Even though I created a getter (stat relation), I think there is a scope to improve the getOrderAmount getter function in model by directly getting the sum amount.
  2. There is a possibility probably to even avoid writing a getOrderAmount getter function in model and try to fetch the initial order amount summary based on the eager load with subquery.
  3. Some amount of query optimization with the relational data is possible instead of a left join or when working with summary columns.

Total 7 comments

#19919 report it
mxd at 2016/07/29 09:51pm
How about the same, but via a join (many-to-many) table?

Thanks for this wiki. It works well for direct related tables.

Now I want to do the same but via an extra join (many-to-many) table in between. So the tables are like; Customer, Customer_Order and Order.

I can't get my head around how to build the query for the filter in the Search model. If anyone can give an example or point me in the right direction it would be very much appreciated.

So specifically the following part;

$query = Customer::find()...
$subQuery = Order::find()...

Where to put the Customer_Order join (many-to-many) table part?

#19017 report it
Kartik V at 2015/02/25 01:40pm
Re: Improvement

@guillemc - thanks for the update... yes there's a Potential Improvements section at the bottom of the wiki that I included specifically where I discussed addressing some of these additional optimizations.

#19016 report it
guillemc at 2015/02/25 11:26am

First of all, thanks for this wiki.

You could take advantage of the fact that $orderAmount is already calculated in the search query, so there is no need to fetch it again for every row.

To do this, you would move the public $orderAmount attribute to the main model, and then change the search query so that it automatically populates it:

$query = Customer::find()->select(['tbl_customer.*', 'orderSum.orderAmount']);
$subQuery = Order::find()
    ->select('customer_id, SUM(amount) as orderAmount')
$query->leftJoin(['orderSum' => $subQuery], 'orderSum.customer_id = id');
#17211 report it
Kartik V at 2014/05/12 11:28pm
Using a STAT like relation

There is an issue I reported on using a STAT like relation to fetch summary... will wait for a response and update accordingly.

#17210 report it
Kartik V at 2014/05/12 11:07pm
Request to add to the wiki

If you find any suggestions for improvement in your usage - do feel free to suggest and improve this wiki.

#17209 report it
Kartik V at 2014/05/12 11:06pm

@realtebo - yes I had suggested some areas for potential improvements at the end of the wiki. Need some time and change to some code as I have not tested this wiki code personally.

#17206 report it
realtebo at 2014/05/12 02:14pm
Really ? There is not another more simple way?

Is this really the only way to create a sortable stat column ?

In Yii 1 was more confortable...

But thanks for tutorial, I'll try it

Leave a comment

Please to leave your comment.