- Example Structure
- Prerequisites
- Gridview Scenarios
- Scenario 1 Steps
- Scenario 2 Steps
- Scenario 3 Steps
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:
- Person: The base model for _tblperson
- PersonSearch: The search and filtering model for Person within gridview.
- Country: The base model for _tblcountry.
- 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 _firstname and _lastname 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 _countryid using foreign key relation with the _tblcountry.
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 _parentid column with id in the _tblperson.
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.
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.
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.
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; }
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?
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 :-).
Thanks :)
It works but now I have a "%" in my search field :(
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; }
Nice!
Very useful tutorial :)
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 JOINcategories
parent
ONcategories
.parent_id
=parent
.categories_id
WHEREcategories_id
='23'**Where do I put the table alias for categories_id in the WHERE statement.
Thnx
Self Join
Updated wiki for self join case. Refer Scenario # 3.
Thnx for the Self Join Update
Good Work KArtik.
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
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.
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.
Corrected embedded wiki link
The embedded wiki link is corrected.
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/
Did change the link
I did change that - check the view history - somehow its not updated on the site. Will do that again.
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.
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) ?
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'); }
And Scenario about pivot table ?
Thx for your work.
But it seems the hardest scenario has been omitted.
I spent 2 days (and it is not finish) to solve this problem:
How to filter a related model (via a pivot table)
I have model A, model A_B, and model B. A_B is the pivot (id, a_id, b_id)
I display in a gridview all A instances and i collect easyly related B model.
But completly unaware how to filter those B model.
I tried to write a ASearchModel but failed.
Do you have a scenario for this ?
Thx
Generalize Scenario 2
Great tutorial,
but I would like to generalize this functionality for many classes because they have the same link to one table.
I would like to use a parent to Model but the Search class inherits the Model, and it seems that
public $countryName; /* setup rules */ public function rules() { return [ /* your other rules */ [['countryName'], 'safe'] ]; }
these new fields can not be moved in a super class of the model class,
because generates a conflict with getCountryName() in Model,
Do you have any suggestion for generalize the procedure for more than one class?
See similar article by Antonio Ramirez
[http://www.yiiframework.com/wiki/653/displaying-sorting-and-filtering-model-relations-on-a-gridview/](http://www.yiiframework.com/wiki/653/displaying-sorting-and-filtering-model-relations-on-a-gridview/ )
Self Join to the same table
Thanks for this nice tutorial!
I had to change the
getParent
relation to be the other way round:/* ActiveRelation */ public function getParent() { return $this->hasOne(self::classname(), ['id' => 'parent_id'])-> from(self::tableName() . ' AS parent'); }
Before - with
['parent_id' => 'id']
- the grid was displaying the child name in the parent row / parent column, and in the child row, the parent field was empty.Regards
Joachim
Example working code
My example
User public function getFName() { return $this->first_name . ' '. $this->middle_name . ' ' .$this->last_name; } UserSearchModel public $fName; ... [['fName'], 'safe'] ... $dataProvider->sort->attributes['fName'] = [ 'asc' => ['first_name' => SORT_ASC, 'middle_name' => SORT_ASC, 'last_name' => SORT_ASC], 'desc' => ['first_name' => SORT_DESC, 'middle_name' => SORT_DESC, 'last_name' => SORT_DESC], 'label' => 'fName', 'default' => SORT_ASC ]; ... $query->andFilterWhere([ 'or', ['like', 'first_name', $this->fName], ['like', 'middle_name', $this->fName], ['like', 'last_name', $this->fName], ['like', 'CONCAT_WS(" ", `first_name`, `middle_name`, `last_name`)', $this->fName]] );
Great tutorial!
I just wonder: How does SQL injection gets prevented for example here:
`
php$q->where('tbl_country.country_name LIKE "%' . $this->countryName . '%"');
`
20010 I agree with this. It should be corrected
Very hard to follow for beginners. Can't figure out what code goes where.
Where the
addCondition
comes from?If you have any questions, please ask in the forum instead.
Signup or Login in order to comment.