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 5 comments

#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