Yii 2.0: Filter & Sort by calculated/related fields in GridView Yii 2.0

38 followers

This wiki explains how to add calculated fields into your Yii Framework 2.0 gridview with filtering and sorting.

Note: If you are looking at filtering and sorting by SUMMARY data from related tables, then refer this wiki.

Example Structure

Let's say you have the following tables:

/* Countries */
CREATE TABLE `tbl_country` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique country identifier',
    `country_name` VARCHAR(150) NOT NULL COMMENT 'Country name',
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Country master table';
 
/* Persons */
CREATE TABLE `tbl_person` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique person identifier',
    `first_name` VARCHAR(60) NOT NULL COMMENT 'First name',
    `last_name` VARCHAR(60) NOT NULL COMMENT 'Last name',
    `country_id` INT(11) COMMENT 'Residing Country',
    `parent_id` INT(11) COMMENT 'Parent person identifier',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Person master table';
 
/* Foreign Key */
ALTER TABLE `tbl_person`
ADD CONSTRAINT `tbl_person_FK1` 
FOREIGN KEY (`country_id`) 
REFERENCES `tbl_country` (`id`) 
, ADD INDEX `tbl_person_FK1` (`country_id` ASC);

Prerequisites

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

  1. Person: The base model for tbl_person
  2. PersonSearch: The search and filtering model for Person within gridview.
  3. Country: The base model for tbl_country.
  4. CountrySearch: The search and filtering model for Country within gridview.

Gridview Scenarios

Let's consider 2 scenarios you want to display in the GridView within the index view generated for Person.

Scenario 1: Calculated field from same table

An example describing how to add a fullName column within the Person grid with sorting and filtering. The field fullName will be concatenation of first_name and last_name separated by space.

Scenario 2: Calculated field from related table

An example describing how to add a countryName column within the Person grid with sorting and filtering. The field countryName will be generated based on country_id using foreign key relation with the tbl_country.

Scenario 3: Self Join to the same table

An example describing how to add a parentName column within the Person grid with sorting and filtering. The field parentName will be the fullName based on self join of the parent_id column with id in the tbl_person.

Scenario 1 Steps

STEP 1: Add a getter function to your base Person model:

Setup base model

/* Getter for person full name */
public function getFullName() {
    return $this->first_name . ' ' . $this->last_name;
}
 
/* Your model attribute labels */
public function attributeLabels() {
    return [
        /* Your other attribute labels */
        'fullName' => Yii::t('app', 'Full Name')
    ];
}

STEP 2: Add an attribute fullName to your model PersonSearch and configure your rules.

Setup search model

/* your calculated attribute */
public $fullName;
 
/* setup rules */
public function rules() {
   return [
    /* your other rules */
    [['fullName'], 'safe']
   ];
}
 
/**
 * setup search function for filtering and sorting 
 * based on fullName field
 */
public function search($params) {
    $query = Person::find();
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);
 
    /**
     * Setup your sorting attributes
     * Note: This is setup before the $this->load($params) 
     * statement below
     */
    $dataProvider->setSort([
        'attributes' => [
            'id',
            'fullName' => [
                'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
                'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
                'label' => 'Full Name',
                'default' => SORT_ASC
            ],
            'country_id'
        ]
    ]);
 
    if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }
 
    $this->addCondition($query, 'id');
    $this->addCondition($query, 'first_name', true);
    $this->addCondition($query, 'last_name', true);
    $this->addCondition($query, 'country_id');
 
    /* Setup your custom filtering criteria */
 
    // filter by person full name
    $query->andWhere('first_name LIKE "%' . $this->fullName . '%" ' .
        'OR last_name LIKE "%' . $this->fullName . '%"'
    );
 
    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'],
        'id',
        'fullName',
        ['class' => 'yii\grid\ActionColumn'],
    ]
]);

Voila, your fullName column in the grid view should be available for sort and filtering.

Scenario 2 Steps

STEP 1: Ensure your Person model has a relation defined to the Country model. You can also implement a getter for CountryName.

Setup base model

/* ActiveRelation */
public function getCountry()
{
    return $this->hasOne(Country::className(), ['id' => 'country_id']);
}
 
/* Getter for country name */
public function getCountryName() {
    return $this->country->country_name;
}
 
/* Your model attribute labels */
public function attributeLabels() {
    return [
        /* Your other attribute labels */
        'fullName' => Yii::t('app', 'Full Name'),
        'countryName' => Yii::t('app', 'Country Name')
    ];
}

STEP 2: Add an attribute countryName to your model PersonSearch and configure your rules.

Setup search model

/* your calculated attribute */
public $countryName;
 
/* setup rules */
public function rules() {
   return [
    /* your other rules */
    [['countryName'], 'safe']
   ];
}
 
/**
 * setup search function for filtering and sorting 
 * based on `fullName` and `countryName` field
 */
public function search($params) {
    $query = Person::find();
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);
 
    /**
     * Setup your sorting attributes
     * Note: This is setup before the $this->load($params) 
     * statement below
     */
     $dataProvider->setSort([
        'attributes' => [
            'id',
            'fullName' => [
                'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
                'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
                'label' => 'Full Name',
                'default' => SORT_ASC
            ],
            'countryName' => [
                'asc' => ['tbl_country.country_name' => SORT_ASC],
                'desc' => ['tbl_country.country_name' => SORT_DESC],
                'label' => 'Country Name'
            ]
        ]
    ]);
 
    if (!($this->load($params) && $this->validate())) {
        /**
         * The following line will allow eager loading with country data 
         * to enable sorting by country on initial loading of the grid.
         */ 
        $query->joinWith(['country']);
        return $dataProvider;
    }
 
    $this->addCondition($query, 'id');
    $this->addCondition($query, 'first_name', true);
    $this->addCondition($query, 'last_name', true);
    $this->addCondition($query, 'country_id');
 
    /* Add your filtering criteria */
 
    // filter by person full name
    $query->andWhere('first_name LIKE "%' . $this->fullName . '%" ' .
        'OR last_name LIKE "%' . $this->fullName . '%"'
    );
 
    // filter by country name
    $query->joinWith(['country' => function ($q) {
        $q->where('tbl_country.country_name LIKE "%' . $this->countryName . '%"');
    }]);
 
    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'],
        'id',
        'fullName',
        'countryName',
        ['class' => 'yii\grid\ActionColumn'],
    ]
]);

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

Scenario 3 Steps

STEP 1: Ensure your Person model has a self join relation defined to itself. You can also implement a getter for ParentName.

Setup base model

/* ActiveRelation */
public function getParent() {
    return $this->hasOne(self::classname(), 
           ['parent_id' => 'id'])->
           from(self::tableName() . ' AS parent');
}
 
/* Getter for person full name */
public function getFullName() {
    return $this->first_name . ' ' . $this->last_name;
}
 
/* Getter for parent name */
public function getParentName() {
    return $this->parent->fullName;
}
 
/* Your model attribute labels */
public function attributeLabels() {
    return [
        /* Your other attribute labels */
        'parentName' => Yii::t('app', 'Parent Name'),
        'fullName' => Yii::t('app', 'Full Name')
    ];
}

STEP 2: Add an attribute parentName to your model PersonSearch and configure your rules.

Setup search model attributes for search

/* your calculated attribute */
public $parentName;
 
/* setup rules */
public function rules() {
   return [
    /* your other rules */
    [['parentName'], 'safe']
   ];
}
 
/**
 * setup search function for filtering and sorting 
 * based on `parentName` field
 */
public function search($params) {
    $query = Person::find();
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);
 
    /**
     * Setup your sorting attributes
     * Note: This is setup before the $this->load($params) 
     * statement below
     */
     $dataProvider->setSort([
        'attributes' => [
            'id',
            'parentName' => [
                'asc' => [
                    'parent.first_name' => SORT_ASC, 
                    'parent.last_name' => SORT_ASC
                 ],
                'desc' => [
                     'parent.first_name' => SORT_DESC, 
                     'parent.last_name' => SORT_DESC
                 ],
                'label' => 'Parent Name',
                'default' => SORT_ASC
            ],
            'fullName' => [
                'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
                'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
                'label' => 'Full Name',
                'default' => SORT_ASC
            ],
            'country_id'
        ]
    ]);
 
    if (!($this->load($params) && $this->validate())) {
        /**
         * The following line will allow eager loading with parent data 
         * to enable sorting by parent on initial loading of the grid.
         */ 
        $query->joinWith(['parent']);
        return $dataProvider;
    }
 
    $this->addCondition($query, 'id');
    $this->addCondition($query, 'first_name', true);
    $this->addCondition($query, 'last_name', true);
    $this->addCondition($query, 'country_id');
    $this->addCondition($query, 'parent_id');
 
    /* Add your filtering criteria */
 
    // filter by parent name
    $query->joinWith(['parent' => function ($q) {
        $q->where('parent.first_name LIKE "%' . $this->parentName . '%" ' .
        'OR parent.last_name LIKE "%' . $this->parentName . '%"');
    }]);
 
    return $dataProvider;
}

STEP 3: Edit your addCondition function in the model PersonSearch

Setup search model condition

protected function addCondition($query, $attribute, $partialMatch = false)
{
    if (($pos = strrpos($attribute, '.')) !== false) {
        $modelAttribute = substr($attribute, $pos + 1);
    } else {
        $modelAttribute = $attribute;
    }
 
    $value = $this->$modelAttribute;
    if (trim($value) === '') {
        return;
    }
 
    /* 
     * The following line is additionally added for right aliasing
     * of columns so filtering happen correctly in the self join
     */
    $attribute = "tbl_person.$attribute";
 
    if ($partialMatch) {
        $query->andWhere(['like', $attribute, $value]);
    } else {
        $query->andWhere([$attribute => $value]);
    }
}

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

Setup view file

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

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

Total 20 comments

#18587 report it
Gadelkareem at 2014/11/22 03:07pm
JoinWith vs InnerJoinWith

A little modification for search and sort to work properly

if ($this-> countryName) {
            $query->innerJoinWith([
                'country' => function ($q) {
                    $q->where('"country"."country_name" ilike \'%' . $this-> countryName . '%\'');
                }
            ]);
        } else {
            $query->joinWith('country');
        }
#18299 report it
EndErr at 2014/10/13 10:18am
Tree like view

Good work! Q1. How to display persons in a grid to distinguish parents and its children in way like a tree view mode (maybe to add a "-" sign in front of FullName) and not to broke the the sense of representation? Q2. Imagine update the AR, you are able to select a parent from a dropdownlist, how to populate that dropdownlist that the current AR would not be in that list (the standard way it will be there too) ?

#17205 report it
Kartik V at 2014/05/12 11:55am
Quotes in link

There were some quotes in the link which was being parsed wrongly by the markdown parser as current link. This is corrected.

#17204 report it
Kartik V at 2014/05/12 11:52am
Did change the link

I did change that - check the view history - somehow its not updated on the site. Will do that again.

#17203 report it
realtebo at 2014/05/12 11:41am
wiki link is not correct !

I'm referring at the link at the top of the article, it point to this very same article !!!!

It points to: http://www.yiiframework.com/wiki/621/filter-sort-by-calculated-related-fields-in-gridview-yii-2-0/

It should point to: http://www.yiiframework.com/wiki/679/filter-sort-by-summary-data-in-gridview-yii-2-0/

#17202 report it
Kartik V at 2014/05/12 11:19am
Corrected embedded wiki link

The embedded wiki link is corrected.

#17194 report it
realtebo at 2014/05/12 06:13am
Thanks, but fix link in the wiki article

Thanks, but the link in the wiki is 'self-refferring', it's point to this very same wiki article.

I'll study it.

#17190 report it
Kartik V at 2014/05/11 10:15am
Wiki for filter & sort by summary fields

I created a new wiki for filtering and sorting by summary data of related models. Since this wiki is large enough.

#17188 report it
realtebo at 2014/05/11 07:51am
Sort by Count / Sum ?

Can you add scenario example for sorting by sql COUNT() o sql SUM() on a related table ?

For example: sort groups by group member count

#16582 report it
dannythebestguy at 2014/03/07 12:31pm
Thnx for the Self Join Update

Good Work KArtik.

#16562 report it
Kartik V at 2014/03/05 08:01pm
Self Join

Updated wiki for self join case. Refer Scenario # 3.

#16543 report it
dannythebestguy at 2014/03/04 05:54pm
Stuck in Self::Join case

Great Tutorial...

But how do I use this for self JOIN in a Grid View

In my Model I have used

public function getParent()
        {           
            return $this->hasOne(self::className(), ['categories_id'=>'parent_id'])->from(self::tableName().' parent');
        }

And in my ModelSearch I have added

$query->joinWith(['parent']);

But when I try to filter any data it throws an exception like :

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'categories_id' in where clause is ambiguous The SQL being executed was: SELECT COUNT(*) FROM categories LEFT JOIN categories parent ON categories.parent_id = parent.categories_id WHERE categories_id='23'

Where do I put the table alias for categories_id in the WHERE statement.

Thnx

#16504 report it
davey` at 2014/02/28 11:18am
Nice!

Very useful tutorial :)

#16486 report it
Kartik V at 2014/02/27 01:13pm
Updated Step 2

@davey I updated Step 2 after a re-consideration. Yes, this is the ideal method, which should load your query the right way (and you do not impact your filters). The following lines of code are updated (similar to what you have done). This should only get executed on first call.

if (!($this->load($params) && $this->validate())) {   
    // will load with country data to allow sort on initial load 
    $query->joinWith(['country']); 
    return $dataProvider;
}
#16485 report it
davey` at 2014/02/27 12:50pm
Thanks :)

It works but now I have a "%" in my search field :(

#16484 report it
Kartik V at 2014/02/27 12:36pm
Sorting on first load

@davey - I updated the tutorial with a Step 4 at the end. A quick dirty method in the controller action forces the action to do a array UNION to the $_GET params. It always thus initializes the countryName (as a %) on first load. Thus allowing sorting to happen without filtering... rather ... actually it is through forced filtering :-).

#16460 report it
davey` at 2014/02/26 05:53am
Great tutorial

However you need to join the tables before the load statement or else if you happen to sort by country name without any filtering, the related column won't be found since only a simple select on the Person model will be done.

At least that's what I needed to do to make it work.

Edit: If you put the join before the load, it will obviously break filtering. I had to change this in order to make this work:

if (!($this->load($params) && $this->validate())) {
        $query->joinWith(['country']);
        return $dataProvider;
    }

Did I do something wrong?

#16314 report it
Pawan Joshi at 2014/02/09 10:56am
Great, It worked.

Actually I was putting the sorting function in the end, whereas it needs to be put before the line

if (!($this->load($params) && $this->validate())) {
        return $dataProvider;
    }
#16313 report it
Kartik V at 2014/02/09 09:44am
Use namespaced classes

@Pawan_Joshi you need to use properly namespaced classes in your code. Read about PHP namespaces to understand this better.

Your error: Class 'frontend\models\Yii' not found, is because you have not namespaced the Yii class rightly in your model class.

You must refer the complete namespaced Yii class or add this to the top of your model class

use Yii;
 
// reference to Yii class
'fullName' => Yii::t('app', 'Full Name')

For sorting - check if you have added the sorting attributes the right way as described. I think you are setting the sorting attributes at a wrong place in your code or passing it wrongly.

#16312 report it
Pawan Joshi at 2014/02/09 09:25am
Great wiki, but sorting not working

I have created all the tables from the scratch as per your guide.

If I am using

'fullName' => Yii::t('app', 'Full Name')

then I am getting the error like: Class 'frontend\models\Yii' not found

but if I am using it simply like~~~

fullName=>'Full Name',

I am getting the colum person with values, filter also working, but sorting is not working.

Leave a comment

Please to leave your comment.

Write new article