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

#20156 report it
Cozumel at 2018/01/08 04:55pm

@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]);
#20155 report it
Néstor Acevedo at 2018/01/08 04:06pm
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]);
#20148 report it
s.oli at 2017/12/14 10:45am
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

The SQL being executed was: SELECT COUNT(*) FROM `person` LEFT JOIN `call` ON `person`.`id` = `call`.`person_id` ORDER BY `created_at` DESC

any tipps?

#20109 report it
Néstor Acevedo at 2017/07/27 03:34pm
'value' => 'city.name' not working


'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.

#20107 report it
lowroller at 2017/07/18 04:18am
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],
#20106 report it
Bianchi at 2017/07/17 12:53pm
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.

#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