Yii 1.1: Searching and sorting by related model in CGridView

97 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

#18763 report it
Kianoosh at 2014/12/31 05:45pm
easy way

really good solution, but a while ago I had similar problem with filtering and joined tables, I just wrote a new MySQL View (join needed tables) in my db then and a new model in Yii

#18755 report it
marcovtwout at 2014/12/29 06:16am
Alternative for view code and sorting

Instead of using the search name field and rewriting the value attribute, you could do it the other way around:

'columns' => array(
    array(
        'name' => 'author.username',
        'filter' => CHtml::activeTextField($model, 'author_search'),
    ),

This also makes the sorting code easier:

'sort' => array(
    'attributes' => array(
        '*', 'author.username',
    ),
    'defaultOrder' => array(
        'author.username' => CSort::SORT_ASC,
    ),
),
#18754 report it
marcovtwout at 2014/12/29 06:13am
Alternative for cleaner code

Instead of adding a search scenario, attributes and related functions to your main model, consider creating and using a separate model like so:

class SearchPost extends Post
{
    public $author;
 
    public function rules()
    {
        return array(
            array( 'xxx,yyy,author', 'safe' ),
        )
    );
 
    public function getSearchCriteria()
    {
        $criteria = new CDbCriteria;
        $criteria->....
 
        return $criteria;
    }
}

Controller action:

$searchModel = new SearchPost;
$searchModel->unsetAttributes();  // clear any default values
if (isset($_GET['SearchPost')) {
    $searchModel->attributes = $_GET['SearchPost'];
}
 
$dataProvider = new CActiveDataProvider('Post', array(
    'criteria' => $searchModel->getSearchCriteria(),
    ... // set pagination/sort here or in view file
));
 
$this->render('admin',array(
    'model' => $searchModel,
    'dataProvider' => $dataProvider,
));
#18125 report it
MasMo at 2014/09/13 05:10am
Great!!!

Thank's ,.,.

#17783 report it
Kostas Apazidis (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

Leave a comment

Please to leave your comment.

Write new article