Yii 2.0: Displaying, Sorting and Filtering Model Relations on a GridView

21 followers

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.

Total 11 comments

#19923 report it
open-ecommerce.org at 2016/08/02 11:57am
fantastic

thanks a lot

#19905 report it
xReprisal at 2016/07/13 02:13am
Aliases

For those looking for aliases. It is done like that $query->joinWith(['city city_alias']);

More about it here

#19817 report it
Maine Mike at 2016/03/29 09:05am
Setting Default Order

When I specify a 'defaultOrder', I always make it match one of the entries in the $dataProvider->sort->attributes array.

#19816 report it
Programmer Thailand at 2016/03/29 08:35am
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

#19814 report it
dubby at 2016/03/28 07:28am
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?

#19785 report it
Sebastian at 2016/02/22 12:22pm
Great help!

This guide is much better than Kartiks one! Thanks!

#19529 report it
darioo at 2015/08/24 05:49pm
Awesome

Helped me a lot :)

#18914 report it
Lifelogger at 2015/02/03 05:41pm
Excellent!

This was just what I was looking for. This article really explains it excellent!

Thank you!

#18855 report it
ilaiya at 2015/01/24 12:00am
Useful

Thnx

#18536 report it
Anil Singh Bafila at 2014/11/12 04:42am
Thanks

Thanks This Post is very Helpful

#18519 report it
White at 2014/11/09 07:32am
thx

This post helps me.

Leave a comment

Please to leave your comment.

Write new article