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

Tutorials

2.0

wiki, faq, grid, gridview, yii2, dfilter,gridview,faq,sort,DatacColumn, calculated, filter, sortyii2,wiki,grid,calculated

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]("http://www.yiiframework.com/wiki/679/filter-sort-by-summary-data-in-gridview-yii-2-0/"). Example Structure -----------------
 
Let's say you have the following tables: ~~~
 
[sql]
 
/* Countries */
 
**Countries**
 
 
```sql
CREATE TABLE `tbl_country` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique country identifier',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Country master table';

 
/* Persons */```
 
 
**Persons**
 
 
```sql
CREATE TABLE `tbl_person` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique person identifier',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Person master table';

 
/* ```
 
 
**Foreign Key */**
 
 
```sql
ALTER TABLE `tbl_person`
ADD CONSTRAINT `tbl_person_FK1`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_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 tableScenario 1 Steps
----------------

 
**STEP 1:** Add a getter function to your base _Person_ model: ### Setup base model
```php

/* Getter for person full name */
public function getFullName() {**STEP 2:** Add an attribute _fullName_ to your model _PersonSearch_ and configure your rules.
### Setup search model

```php

/* your calculated attribute */
public $fullName;**STEP 3:** Configure your gridview columns in your view _index_ file
### Setup view file

```php

echo GridView::widget([
'dataProvider' => $dataProvider,**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

```php

/* ActiveRelation */
public function getCountry()**STEP 2:** Add an attribute _countryName_ to your model _PersonSearch_ and configure your rules.
### Setup search model

```php

/* your calculated attribute */
public $countryName;**STEP 3:** Configure your gridview columns in your view _index_ file
### Setup view file

```php

echo GridView::widget([
'dataProvider' => $dataProvider,**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

```php

/* ActiveRelation */
public function getParent() {**STEP 2:** Add an attribute _parentName_ to your model _PersonSearch_ and configure your rules.
### Setup search model attributes for search

```php

/* your calculated attribute */
public $parentName;**STEP 3:** Edit your _addCondition_ function in the model _PersonSearch_
### Setup search model condition

```php

protected function addCondition($query, $attribute, $partialMatch = false)
{**STEP 4:** Configure your gridview columns in your view _index_ file
### Setup view file

```php

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