YII联表查询

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 incolumn, allow sort on this column and probably filtering bysubstring of author name. Best solution (in my opinion) to provideall this functionalities is:

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

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 standardimplementation - there is a search() function in every model). Alsowe have to specify that we want to fetch Post models together withrelated Author by setting 'with' attribute of criteria (this waythere will be only one database query with join instead of manyqueries 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 trickyfeature 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 overloaddefault searching. This configuration says, that when user wants tosort by 'author_search' field it should be done like specified.last entry '*' says that every other field of this model should betreated normally. This way you can override also default attributessorting (for example specify that when user sorts by last_namecolumn there should be applied sorting by last_name and first_namecolumn 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 foreignkey column and we have search by user name substring.

原文地址http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/#c14784