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 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.

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