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

This web-tip 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 web-tip.

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 id',
    `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 id',
    `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;
    }

    $query->andFilterWhere(['id' => $this->id]);
    $query->andFilterWhere(['like', 'first_name', $this->first_name]);
    $query->andFilterWhere(['like', 'last_name', $this->last_name]);
    $query->andFilterWhere(['country_id' => $this->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;
    }

    $query->andFilterWhere(['id' => $this->id]);
    $query->andFilterWhere(['like', 'first_name', $this->first_name]);
    $query->andFilterWhere(['like', 'last_name', $this->last_name]);
    $query->andFilterWhere(['country_id' => $this->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;
    }

    // Note the `tbl_person` prefix
    $query->andFilterWhere(['tbl_person.id' => $this->id]);
    $query->andFilterWhere(['like', 'tbl_person.first_name', $this->first_name]);
    $query->andFilterWhere(['like', 'tbl_person.last_name', $this->last_name]);
    $query->andFilterWhere(['tbl_person.country_id' => $this->country_id]);
    $query->andFilterWhere(['tbl_person.parent_id' => $this->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: 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.

7 thoughts on “Filter & Sort by calculated/related fields in GridView Yii 2.0

  1. Hi,

    I tried implementing scenario 2 and I’m getting the following error message when I try to search on the relations column in gridview:

    Calling unknown method: app\models\OrganizationSearch::addCondition()

    There seems to be something wrong with this line:
    $this->addCondition($query, ‘id’);

    Can you please tell me how to fix this? Thanks very much in advance.

    1. This web tip was created when yii2 framework was at beta stage. The GII code generated then by yii, used to have the addCondition method in the SearchModel then.

      The web tip / article is now updated to reflect how you would do it in the latest version of yii.

  2. I’m stuck in a more complex variation of this problem: I’ve got a table billing which contains a foreign key customer_id to table customer. The table customer contains a foreign key organization_id to table organization. How do I join these three tables together?

    This doesn’t work, also tried a viaTable construct, doesn’t work either, always gives error message app\models\Billing has no relation named “organization”

    if (!($this->load($params) && $this->validate())) {
    $query->joinWith([‘customer’]);
    $query->joinWith([‘organization’])
    return $dataProvider;
    }

  3. Hey, do you have any instructions on how to do Scenario 2 with MongoDB? For filtering, joinWith isn’t available in the activeQuery/activeRecord implementation of Yii MongoDB. Also, sorting isn’t working because I can’t seem to find a way to access the join collection which is used.

Comments are closed.