One of the things you will find tricky to implement is the the sorting and filtering of a GridView's column that displays related model data.
As you know if you have been playing with Yii2 lately, there is a new proposed way to search for data and is by using objects that extend from the main entity models and mark the searchable attributes as "safe". So, how do we sort and filter related data on a GridView widget?
Lets imagine we have the following relations of a model named "Tour":
/**
* @return \yii\db\ActiveQuery
*/
public function getCountry()
{
return $this->hasOne(Country::className(), ['id' => 'country_id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getCity()
{
return $this->hasOne(City::className(), ['id' => 'city_id']);
}
And we wish to display the name of the country and the name of the city on a GridView. To do that, we do the following on our "TourSearch" model:
class TourSearch extends Tour // extends from Tour see?
{
// add the public attributes that will be used to store the data to be search
public $city;
public $country;
// now set the rules to make those attributes safe
public function rules()
{
return [
// ... more stuff here
[['city', 'country'], 'safe'],
// ... more stuff here
];
}
// ... model continues here
Now we will be able to setup our GridView so to display the related data:
// ... more grid configuration here
'columns' => [
// ... more columns configuration here
[
'attribute' => 'city',
'value' => 'city.name'
],
[
'attribute' => 'country',
'value' => 'country.name'
],
// ... more stuff here
By doing as explained above, we will be able to display data but how to sort or filter? Lets explain that by example, and this time lets focus on the "search" method of our "TourSearch" class:
public function search($params)
{
// create ActiveQuery
$query = Tour::find();
// Important: lets join the query with our previously mentioned relations
// I do not make any other configuration like aliases or whatever, feel free
// to investigate that your self
$query->joinWith(['city', 'country']);
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
// Important: here is how we set up the sorting
// The key is the attribute name on our "TourSearch" instance
$dataProvider->sort->attributes['city'] = [
// The tables are the ones our relation are configured to
// in my case they are prefixed with "tbl_"
'asc' => ['tbl_city.name' => SORT_ASC],
'desc' => ['tbl_city.name' => SORT_DESC],
];
// Lets do the same with country now
$dataProvider->sort->attributes['country'] = [
'asc' => ['tbl_country.name' => SORT_ASC],
'desc' => ['tbl_country.name' => SORT_DESC],
];
// No search? Then return data Provider
if (!($this->load($params) && $this->validate())) {
return $dataProvider;
}
// We have to do some search... Lets do some magic
$query->andFilterWhere([
//... other searched attributes here
])
// Here we search the attributes of our relations using our previously configured
// ones in "TourSearch"
->andFilterWhere(['like', 'tbl_city.name', $this->city])
->andFilterWhere(['like', 'tbl_country.name', $this->country]);
return $dataProvider;
}
Thats it... Hope this tutorial helps you find your way around.
thx
This post helps me.
Thanks
Thanks
This Post is very Helpful
Useful
Thnx
Excellent!
This was just what I was looking for. This article really explains it excellent!
Thank you!
Awesome
Helped me a lot :)
Great help!
This guide is much better than Kartiks one! Thanks!
The filter input box not appearing for me.
I've followed the simple tutorial above but the filter input box in the search row is not appearing for me. Have I missed a step?
Thank you for this tutorial and how to set default sort from relation model?
i'v problem with set default sort from relation model
$dataProvider->sort = [
'defaultOrder' => [ 'tbl_city.name' => SORT_ASC, ]
]
it's not working
Setting Default Order
When I specify a 'defaultOrder', I always make it match one of the entries in the $dataProvider->sort->attributes array.
Aliases
For those looking for aliases. It is done like that
$query->joinWith(['city city_alias']);
More about it here
fantastic
thanks a lot
Didn't work for me
I followed all steps and can see the filter textbox, can see the names in the column, but when I try to search some name, I get ALL rows returned... as I havent put any string in the textbox!
Filter input box not appearing
If you are having this problemn don't forget do put your attribute as safe in the rules section of your model.
Thanks, and an easier way of adding sort details
Thanks, I found this tremendously useful - scenario 2 solved my problems perfectly. However instead of defining $dataProvider->setSort as shown, which would have required resupplying a lot of details for the other sortable columns that had already been set up automatically, I just added the details for my calculated field to it like this:
$dataProvider->sort->attributes['projectcode'] = [ 'asc' => ['projectcode' => SORT_ASC], 'desc' => ['projectcode' => SORT_DESC], ];
'value' => 'city.name' not working
For
'value' => 'city.name'
this is displaying the literal 'city.name' not the required value stored, so to show the value I have to call the model City or use
to get the value.
Thanks, but one thing ...
Hi,
it works good, but i have a probleme when i want to sort in the grid view an a related column.
i get an sql access violation error
any tipps?
Not working more on Yii 2.0.13
This post solution is not working anymore on Yii 2.0.13 because I am getting Trying to get property of non-object if city and country tables are empty. So it must have a validation to get if both variables are or not empty:
if(null !== $this->city && null !== $this->country) { $query->andFilterWhere(['like', 'tbl_city.name', $this->city->name]) ->andFilterWhere(['like', 'tbl_country.name', $this->country->name]); }
Title
@Nestor your solution isn't great because if one field has a value and the other is null they'll both fail. You probably also want to turn your conditional into an OR, not an AND.
Something like
if(null !== $this->country) { $query->orFilterWhere(['like', 'tbl_country.name', $this->country->name]); } if(null !== $this->city) { $query->orFilterWhere(['like', 'tbl_city.name', $this->city->name]); }
It is very helpful!
Thanx a lot.
'asc' => ['tbl_city.name' => SORT_ASC],
for me is enough use only the attribute without a table name
'asc' => ['name' => SORT_ASC],
I would want to use
country_name
andcity_name
in place ofcountry
andcity
as the additional public attributes in theTourSearch
model, because the former would not share the same names of the relation properties ofTour
model. Althoughcountry
andcity
should work as expected, they are a little misleading.This is all tooo complicated and might not work in some cases. Im my case I had to make view of two joined tables (INNER JOIN tabkename ON ...). Then make Model for the view and SearchModel as usual. Once you have view all is behaving as normal table. ONLY in GridView I had to modify url of the ActionColumn as it was unable to detect the ID the key of the view:
'urlCreator' => function ($action, $model, $key, $index) { return Url::to(['SomeAction/'.$action, 'id' => $model->id]); },
My sorting not working don't know the reason.
GridView
Rules
SearchModal.php
Can some explain why the publish date is not sorting.
Great article
[ 'attribute' => 'city', 'value' => 'city.name' ], [ 'attribute' => 'country', 'value' => 'country.name' ],
Don't forget to add this otherwise it not show search bar in view
this helps me so much. thanks
Very useful post, thanks.
Sometimes field name conflicts in On clause then simply use ->join() no harm.
Regards,
If you have any questions, please ask in the forum instead.
Signup or Login in order to comment.