Searching and sorting by related model in CGridView Follow @yiiframework

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' ),
        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

#13158

Jose H. Milán at 2013/05/09 09:14am

Really good stuff.

Thank you mate

#12666

Bono at 2013/04/04 07:41am

Searching and sorting by related model in CGridView

Thanks! Help me a lot!

#11780

le_top at 2013/02/03 03:55pm

Behavior to make relation search easier

I just released a behavior to help with this: Related Search Behavior

#11111

Jimlam at 2012/12/17 12:03pm

Null values in the related fields

@ Evegeniy

Sorry I am a bit late :)

array('name'=>'name', 'value'=>'$data->yourrelation!==null?$data->yourrelation->attributeinrelatedtable:"None"'),

#10049

Evgeniy Tkachenko at 2012/10/02 05:26am

PHP Error Description Trying to get property of non-object

@Jimlam

I Figured Out The Cause Of The Problem: it is because some of the fields have null values. Do you have a solution to this problem? Thanks

array(
 'name'=>'author_search',
 'value'=>'(isset($data->author->username)) ? $data->author->username : ""' 
),

#8807

redguy at 2012/06/28 07:44am

Re: Displaying array of values for one to many relation between 2 tables

1- to display multiple values you will have to prepare custom pseudo-attribute (getter):

public function getRelatedObjectNames() { return implode( ', ', CHtml::listData( $this->relatedObjects, 'id', 'name' ) ); }

or something like this. you may access it simply by

echo $model->relatedObjectNames; or in grid column: array( 'value'=>'$data->relatedObjectNames' ),

2- filtering by such column requires sub-query in search criteria:

if( !empty( $this->relatedNames_filter ) ) { $criteria->addCondition( 't.id IN (SELECT rel.foreign_id FROM related_object rel WHERE rel.name LIKE :relNameLike)' ); $criteria->params[':relNameLike'] = '%' . $this->relatedNames_filter . '%'; }

...or something like this :)

3- sorting by such field is rather impossible...

#8806

Jimlam at 2012/06/28 04:45am

Displaying array of values for one to many relation between 2 tables

Hi Redguy,

I would like to do the same thing with 2 tables having a 1-many relations. Let's say we invert the example you gave.

In the cgrid view of author, we have a column displaying the various posts for each author. Of course, this approach is only when there are not many posts for each author. I have such a relation where there could be at most 5 related records for each primary record. Can you suggest something? Thanks

#8792

Jimlam at 2012/06/27 01:49am

Problem with 2 related tables: null values in the related field cause the error

@Redguy,

How do I post you on priv? :)

#8757

redguy at 2012/06/25 05:50am

@Bianca

Get whole callstack and check if this error is not related to grid visualisation (value getters, etc). You can also post me on priv this callstack, controller, model and view code.

#8749

Jimlam at 2012/06/23 02:59pm

Problem with 2 related tables: null values in the related field cause the error

@Redguy,

I figured out the cause of the problem: it is because some of the fields have null values. Do you have a solution to this problem? Thanks

#8746

Jimlam at 2012/06/23 08:50am

Problem with 2 related tables

@Redguy,

I have done the necessary modifications for 2 related tables but I am having the following error:

**PHP Error

Description

Trying to get property of non-object

Source File

C:\yii1.1.4\framework\base\CComponent.php(616) : eval()'d code(1) No source code available.**

Necessary modifications done as follows:-

$criteria->with = array('cli','agence');
 
 
 
        return new CActiveDataProvider(get_class($this), array(
 
            'criteria'=>$criteria,
            'sort'=>array(
                'attributes'=>array(
                'client_name'=>array(
                    'asc'=>'cli.name',
                    'desc'=>'cli.name DESC',
                ),
                'agent'=>array(
                    'asc'=>'agence.agency_name',
                    'desc'=>'agence.agency_name DESC',
                ),              
                '*',
                ),
            ),          
 
        ));

Relations are named cli and agence. client_name and agent are the 2 public variables created in the model. Can you please help? Thanks

#8622

redguy at 2012/06/14 11:19am

@mohamadaliakbari

"t" is default alias for primary table in AR query. you can change it with criteria. for related tables their alias is by default relation name. you can run profiler with sql profiling enabled and see real queries that are executed when dealing with AR in Yii - it is very handy to understand they way Yii does it :)

#8621

PrplHaz4 at 2012/06/14 11:17am

Relational ActiveRecord Table Alias

t is only for the primary table, use the relation name to prefix the related records:

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#disambiguating-column-names

#8620

mohamadaliakbari at 2012/06/14 11:05am

@redguy

is "t." always the table alias?

#8619

redguy at 2012/06/14 10:48am

@mohamadaliakbari

When dealing with joined tables always use table alias to compare attributes:

$criteria->compare( 't.id', $this->id );
or
$criteria->compare( 'author.id', $this->id );

this way you point exactly whicz table you are referring. In your case - you have "id" column in both tables and DB does not know which one you are referring in search clause.

#8618

mohamadaliakbari at 2012/06/14 10:39am

I have trouble with ID column

everything works great except when I try search on ID column, I got this error:

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `who_company` `t` LEFT OUTER JOIN `users` `user` ON (`t`.`user_id`=`user`.`id`) LEFT OUTER JOIN `profiles` `profile` ON (`profile`.`user_id`=`user`.`id`) WHERE (id=:ycp0)

#8542

Rahmat Awaludin at 2012/06/11 09:00am

#7400

7400

don't forget to add criteria to

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

this way, sorting will work! :D

#8422

MadAnd at 2012/06/01 12:19pm

@saegeek: Re: How to combine with a custom formatter

You need to properly quote value element, because it's being passed to CComponent::evaluateExpression (though to PHP's eval function):

array(
    'name'=>'related_product',
    'type'=>'ProductUrl',
    'value'=>'array($data->product->id, $data->product->title)',
),

#8329

saegeek at 2012/05/26 01:56am

How to combine with a custom formatter

I created a custom CFormatter class: in config/main.php:

'components'=>array(
'format'=>array(
            'class'=>'application.extensions._urlFormatter'
        ),

The class:

<?php
class _urlFormatter extends CFormatter
{
    public function formatProductUrl($value)
    {
        return CHtml::link($value[1],Yii::app()->createUrl('products',array('id'=>$value[0],'title'=>$value[1])),array('target'=>'_blank'));
    }
}

and of course i defined 'value' as an array:

array(
            'name'=>'related_product',
            'type'=>'ProductUrl',
            'value'=>array('$data->product->id','$data->product->title'),
            ),

But i got CExceptions, evaluateExpression() method fails in CDataColumn about my 'value'.

How i can pass to my custom CFormatter an array of relational values ?

#8289

Gerhard Liebenberg at 2012/05/23 05:12am

Re: $this->author->username

Thanx for the good answer Redguy.

Please login to leave your comment.

Searching and sorting by related model in CGridView Follow @yiiframework

Total 20 comments

Leave a comment

Yii Supporters