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


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
        //... 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 12 comments

#19941 report it
DarkSlash at 2016/09/09 05:18pm
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!

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

thanks a lot

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

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

More about it here

#19817 report it
Michael Labriola 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

Helped me a lot :)

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

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


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

Thanks This Post is very Helpful

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

This post helps me.

Leave a comment

Please to leave your comment.

Write new article