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

#19079 report it
le_top at 2015/03/09 06:34am
Please continue in forum

When leaving a comment, it is suggested to:

Please only use comments to help explain the above article. If you have any questions, please ask in the forum, instead.

To avoid non-existing objects in some cases you can also write:

array( 'name'=>'search_name', 
  'value' => 'CHtml::value($date,"teachers2.user_fname")',
  'filter' => CHtml::activeTextField($model, 'search_name'),
),

The test inside 'CHtml::value' is more complete and it is easier to write (no repetition).

#19077 report it
bandpay at 2015/03/09 05:02am
@iamjuliano

hi, do you have some sample data from both tables which you mind posting here? I think that you don't have corresponding data in your child table and please make sure that you are seeing all the errors.

#19076 report it
lgastmans at 2015/03/09 04:42am
try this

have a look here

#19075 report it
iamjuliano at 2015/03/09 04:30am
@bandpay

i removed my addCondition in my model

public function search()
    {
        // @todo Please modify the following code to remove attributes that should not be searched.
 
        $criteria=new CDbCriteria;
        $criteria->with = array( 'teachers2' );
        $criteria->compare( 'user_fname', $this->search_name, TRUE );
 
        $criteria->compare('id',$this->id);
        $criteria->compare('tid',$this->tid);
        $criteria->compare('status',$this->status,true);
        $criteria->compare('status_date',$this->status_date,true);
 
        // $criteria->addCondition('status = "login"');
        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
 
            // 'sort'=>array(
                // 'defaultOrder'=>'status_date DESC',
              // ),
              'sort'=>array(
        'attributes'=>array(
            'author_search'=>array(
                'asc'=>'teachers2.user_fname',
                'desc'=>'teachers2.user_fname DESC',
            ),
            '*',
        ),
    ),
        ));
    }

this is in my gridview

array( 'name'=>'search_name', 
  'value' => 'isset($data->teachers2) ? $data->teachers2->user_fname : null',
  'filter' => CHtml::activeTextField($model, 'search_name'),
                    ),

it is still not working. waahh! what seems to be the problem..

:(

#19074 report it
bandpay at 2015/03/09 04:09am
@iamjuliano

hi, this is difficult to say why it is not working. If you have data in your tables and you can run a piece of SQL which could get the data out with the criteria you have chosen, then I can't say why the data is not emerging. But I have an vague idea that " status = login" or "tid" are making a little trouble.

Try to start with as few conditions as possible to pinpoint the reason for the not working search.

#19073 report it
iamjuliano at 2015/03/09 03:57am
@bandpay

thanks babak!

im not getting the error but the search does'nt work..

what could be the problem?

#19072 report it
bandpay at 2015/03/09 03:43am
@iamjuliano

Hi Juliano,

You can do this in your GridView at the value: It happens when the foreign-key can be NULL
instead of this: 'value'=>'$data->teachers2->user_fname', put this and you are good to go.

'value' => function($data) {
    return isset($data->teachers2) ? $data->teachers2->user_fname : null;
}

Sincerely, Babak

#19071 report it
iamjuliano at 2015/03/09 03:25am
Getting error!

im getting "Trying to get property of non-object"

here's my code :

model:

public $search_name;
 
public function rules()
    {
    return array(   
         array('id, tid, status, status_date, search_name', 'safe', 'on'=>'search'),);
    }
 
public function relations()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'teachers2'=>array( self::BELONGS_TO, 'teachers2', 'tid' ),
        );
    }
 
public function search()
    {
        // @todo Please modify the following code to remove attributes that should not be searched.
 
        $criteria=new CDbCriteria;
        $criteria->with = array( 'teachers2' );
        $criteria->compare( 'user_fname', $this->search_name, TRUE );
 
        $criteria->compare('id',$this->id);
        $criteria->compare('tid',$this->tid);
        $criteria->compare('status',$this->status,true);
        $criteria->compare('status_date',$this->status_date,true);
 
        $criteria->addCondition('status = "login"');
        return new CActiveDataProvider($this, array(
            'criteria'=>$criteria,
 
            // 'sort'=>array(
                // 'defaultOrder'=>'status_date DESC',
              // ),
              'sort'=>array(
        'attributes'=>array(
            'author_search'=>array(
                'asc'=>'teachers2.user_fname',
                'desc'=>'teachers2.user_fname DESC',
            ),
            '*',
        ),
    ),
        ));
    }

my gridview

$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'teacher-status-grid',
    'dataProvider'=>$model->search(),
    'filter'=>$model,
        'columns'=>array(
 
        array( 'name'=>'search_name', 
        'value'=>'$data->teachers2->user_fname', 
        'filter' => CHtml::activeTextField($model, 'search_name'),
                    ),
 
),
);

PLEASE HELP..

#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 !

Leave a comment

Please to leave your comment.

Write new article