Yii 1.1: Searching and sorting by related model in CGridView

95 followers

Lets say we have two models and relation between them:

class Author extends CActiveRecord {
...
}
 
class Post extends CActiveRecord {
...
    function relations() {
        return array(
            'author'=>array( self::BELONGS_TO, 'Author', 'id_author' ),
        );
    }
...
}

when you list Posts in grid you would like to print author name in column, allow sort on this column and probably filtering by substring of author name. Best solution (in my opinion) to provide all this functionalities is:

First you have to add new attribute to Post model, where search string will be stored. You could use foreign key column to achieve same effect, but I preffer not to overload meaning of this column as in search scenario you will store there string, not foreign id. You have to add this new attribute to 'safe' attributes in search scenario.

class Post extends CActiveRecord {
  public $author_search;
  ...
  public function rules() {
    return array(
      ...
      array( 'xxx,yyy,author_search', 'safe', 'on'=>'search' ),
    );
  }
}

Now we have to use this attribute in search criteria (in standard implementation - there is a search() function in every model). Also we have to specify that we want to fetch Post models together with related Author by setting 'with' attribute of criteria (this way there will be only one database query with join instead of many queries fetching related authors in lazy load mode):

$criteria = new CDbCriteria;
$criteria->with = array( 'author' );
...
$criteria->compare( 'author.username', $this->author_search, true );
...

And since we are editing search function - lets add another tricky feature to returned CActiveDataProvider:

return new CActiveDataProvider( 'Post', array(
    'criteria'=>$criteria,
    'sort'=>array(
        'attributes'=>array(
            'author_search'=>array(
                'asc'=>'author.username',
                'desc'=>'author.username DESC',
            ),
            '*',
        ),
    ),
));

'attributes' section of sort configurations lets us overload default searching. This configuration says, that when user wants to sort by 'author_search' field it should be done like specified. last entry '*' says that every other field of this model should be treated normally. This way you can override also default attributes sorting (for example specify that when user sorts by last_name column there should be applied sorting by last_name and first_name column together)

Now we have prepared everything for our grid:

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'=>$model->search(),
    'filter'=>$model,
    'columns'=>array(
        'title',
        'post_time',
        array( 'name'=>'author_search', 'value'=>'$data->author->username' ),
        //post strictly required user but in any case that model has not required user(or another relation) you should replace with it
        array( 'name'=>'author_search', 'value'=>'$data->author ? $data->author->username: "-"' ),
        array(
            'class'=>'CButtonColumn',
        ),
    ),
));

Thats it. We have sorting by user name instead of id_user foreign key column and we have search by user name substring.

Total 20 comments

#17783 report it
KonApaz at 2014/07/22 08:51am
RE: #17781

Hi vijay

what excactly you mean ? search user on filters ?

Please post a related thread on the forum with your code!

#17781 report it
vijay p s at 2014/07/22 08:30am
Error

Its Working!! but When I use the filters is not work..

#16655 report it
Emil Fedorciuc at 2014/03/16 08:02am
re: Relations through many tables

Thank you le_top

Great extension: works right out of the box, easy setup, cleaner code.

Took me a while to figure out that I was using a HAS_MANY relation, but after I defined another relation to HAS_ONE it worked.

#16654 report it
le_top at 2014/03/16 05:24am
Relations through many tables

Dear Emil Fedorciuc

No need to panic regarding your need (table1->table2->...->table5).

What softark explains (how to setup relations, searches, ...) is made easy with an extension that I made which I called RelatedSearchBehavior.

You can have a look at the demo which has 7 layers of linked tables and yet a fully searcheable grid with columns from almost every table. On top of that the demo is running on sqlite! It is quite easy to set up - as the demo page says, look at InvoiceLine.php (the model) and index.php (the view). (Links point to github, but you should get the zip from the extension page for the most current demo). I hope you'll enjoy.

#16652 report it
Emil Fedorciuc at 2014/03/16 03:24am
re: How can I access to a related model since a related model

Thank you softark! You're a life saver, ofc I am too lazy to read all the documentation so I missed that part. Now comes the best part for me, I wander how that works in my next scenario: table1 related to table2, table2 related to table3, table3 related to table4, table4 related to table5. I need search on data from table5 together with table1. The initial design wasn't intended like this, but I guess unless I redesign the tables there's no other way to do this.

#16650 report it
softark at 2014/03/15 05:59am
re: How can I access to a related model since a related model

This is not correct:

$criteria->compare( 'author.family.family_name', $this->family_search, true );

Try this instead:

$criteria->compare( 'family.family_name', $this->family_search, true );

Please read the definitive guide : Relational Active Record - 5. Disambiguating Column Names

Especially the tip column.

#16648 report it
Emil Fedorciuc at 2014/03/15 02:41am
re: How can I access to a related model since a related model

Good luck with that!

I ended up adding a custom dropdown filter:

array(
                    'name'=>'id_author', 
                    'value'=>'$data->author->family->family_name',
filter'=>CHtml::listData(Author::model()->with('family')->findAll(), 'id', 'family.family_name'),
                    ),

You would actually filter the data based on the author id from the Author model and display the data from the related Family model. This is an adaptation of my script to your case, I hope I got the fields right. I couldn't find a way to get a partial search working with an input field. If you find the solution to this problem please share!

#16645 report it
tanebisse at 2014/03/14 09:37am
re: How can I access to a related model since a related model

I've read the documentation but it does not indicate how to configure "Criteria" as in this tutorial. I want to configure search with a model related to a model related to another model. post.author.family.family_name for example.

$criteria = new CDbCriteria;
$criteria->with = array( 'author' );
...
$criteria->compare( 'author.username', $this->author_search, true );
...

I tried

$criteria->compare( 'author.family.family_name', $this->family_search, true );

But it doesn't work !

#16644 report it
redguy at 2014/03/14 09:17am
re: How can I access to a related model since a related model

I suggest you read CGridView documentation:

'columns'=>array(
   'title',          // display the 'title' attribute
   'category.name',  // display the 'name' attribute of the 'category' relation
   'content:html',   // display the 'content' attribute as purified HTML
   ...
#16641 report it
tanebisse at 2014/03/14 06:27am
How can I access to a related model since a related model

For example if i add this class in additionnal to Post and Author :

class Family extends CActiveRecord {
...
}
 
class Author extends CActiveRecord {
...
    function relations() {
        return array(
            'family'=>array( self::BELONGS_TO, 'Family', 'id_family' ),
        );
    }
...
}

And now in a Post CgridView I want to access to a family property. How can I do that ?

#16105 report it
newscloud at 2014/01/20 01:59pm
More sample code on using relations in CGridView

I've posted sample code here that's similar to this tutorial: http://jeffreifman.com/yii/cgridview/

It has more detail on sorting related columns.

#15988 report it
markslor at 2014/01/07 10:06am
It Works !

Thanks a lot for this tutorial it works fine for me. I have been searching to this solution for two months. Thanks

#15784 report it
J.J at 2013/12/16 11:14pm
@Emil Fedorciuc

Hi Emil, thank you for your response.

I have the self solution, and is working. Here my solution in admin.php

'filter' => CHtml::activeDropDownList($model, 'author_id', CHtml::listData(Author::model()->findAll(), 'id', 'username'), array('prompt' => ' ')),

And all the configuration is the same like redguy did post. Thank you.

#15774 report it
Emil Fedorciuc at 2013/12/16 08:47am
@J.J.

From what I see you do the wrong compare there

You compare the id to the value of that field, if you use text search it will compare the text with the value of the related table.

$criteria->compare( 'author.username', $this->author_search, true );

Your filter should be:

'filter' => CHtml::listData(Author::model()->findAll(), 'username', 'username'),

with your filter the column should be:

array( 
'name'=>'id_author',
'value'=>'$data->author->username',
'filter' => CHtml::listData(Author::model()->findAll(), 'id', 'username'),
 ),

And you shouldn't need all the other related code in this post. The code isn't tested.

#15769 report it
J.J at 2013/12/16 01:03am
Filter CGridView in table relation

Hi redguy, Thanks before for the posting. It is useful to me.

But, if I want to display username in CGridView as filter, there is error.

'filter' => CHtml::listData(Author::model()->findAll(), 'id', 'username'),

If I am using code like above, when one of dropdown is selected, no data result. How about find the solution for my case ?

Thanks..

#15034 report it
Emil Fedorciuc at 2013/09/30 01:21pm
@darioo

your "fix" only works in our example:

$criteria->compare( 'username', $this->author_search, true );

you might use a field that have the same name in both the base table and related table and you will then get a mysql error with that, so I recommend to use related_table.field instead of just field. Always use

$criteria->compare( 'author.username', $this->author_search, true );

for safety

#14784 report it
redguy at 2013/09/09 09:42am
Re: together

like Softark pointed out - it is rather assumed that relation is BELONGS_TO or HAS_ONE, so "together" is not required in such cases. I do not prefer straight joins and searching in HAS_MANY and MAMY_MANY scenarios as it can lead to some side effects (like loading only those dependent records which match criteria). When dealing with multiple dependent records I prefer writing SQL subqueries, so criteria looks like this:

$criteria->addCondition( 't.id IN (SELECT id_article FROM author WHERE username LIKE :uname)' );
$criteria->params[':uname'] = '%' . $this->author_username . '%';

This way I have much more control on what is going on. Also - query returns only 'article' records without any dependent records if I wish it work this way :)

As for related table alias when comparing - Softark just said everything about it :)

#14783 report it
softark at 2013/09/09 08:57am
RE: together and table alias

@Taker You had to set 'together' to true, because you were working with HAS_MANY or MANY_MANY relation. The sample code in this article assumes that the relation is BELONGS_TO, which doesn't need 'together' set to true.

Relational query - lazy loading and eager loading / with and togeter

@darioo Your code will work fine only when the main table doesn't have a column with the same name. It's recommended to use always the table aliases when you are joining tables.

#14781 report it
darioo at 2013/09/09 08:02am
Little fix

This tutorial is great, but this is how it worked for me.

$criteria->compare( 'author.username', $this->author_search, true );

Instead of author.username, I just put username, without that author prefix so:

$criteria->compare( 'username', $this->author_search, true );
#14323 report it
Taker at 2013/08/04 07:31pm
I have to set "together" or it always show column not found

I have to add:

$criteria->together = true;

if not, I'll got an error when sort/search from the CGridView: Column not found

Leave a comment

Please to leave your comment.

Write new article