[转] Searching and sorting a column from a related table in a CGridView
相关资料:
http://www.yiiframework.com/forum/index.php?/topic/8148-cgridview-filter-with-relations/
In this post I'm going to explain how to search/sort a column from a related table in a CGridView by adding a comment list to the Yii Blog demo.
First we'll start with the CommentController which we'll add an action to. We'll name it actionList. The action will look like this:
|
1
2
3
4
5
6
7
8
9
10
11
|
public function actionList(){ $model=new Comment('search'); $model->unsetAttributes(); if(isset($_GET['Comment'])) $model->attributes=$_GET['Comment']; $this->render('list',array( 'model'=>$model, ));} |
This is nothing special. It looks exactly like the admin.php file that is created when you use the CRUD creation.
Now we'll create the view. Create a file named list.php in /protected/views/comment/ and paste this code in it.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<?php$this->breadcrumbs=array( 'Comments',);?><h1>Comment List</h1><?php $this->widget('zii.widgets.grid.CGridView', array( 'dataProvider'=>$model->search(), 'filter'=>$model, 'pager'=>array('pageSize'=>25), 'columns'=>array( 'content', 'status', 'author', ),)); ?> |
This is a basic CGridView which just views the 'content', 'status' and 'author', columns of the comments.
Let's say we want to add the title of the post to which the comment belongs to this list. We'll just add post.title to the list to do that.
|
1
2
3
4
5
6
|
'columns'=>array( 'content', 'post.title', 'status', 'author',), |
Now if you run the page and check it out, you'll notice that the post title is visible for each comment.
The problem
If you look at the page again you'll notice that you're not able to sort by the post title. You're also not able to search for the related column. This is basically because the CGridView checks if there is a '.' inside the given column name and if there is, it just makes sure the filter for that column is not displayed.
The solution
To fix this problem we have to do a couple of things. First we'll have to add a getter and a setter to the Comment model. This could look something like this:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
private $_postTitle = null;public function getPostTitle(){ if ($this->_postTitle === null && $this->post !== null) { $this->_postTitle = $this->post->title; } return $this->_postTitle;}public function setPostTitle($value){ $this->_postTitle = $value;} |
Next we'll add the property to the rules function:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
public function rules(){ // NOTE: you should only define rules for those attributes that // will receive user inputs. return array( array('content, author, email', 'required'), array('author, email, url', 'length', 'max'=>128), array('email','email'), array('url','url') array('content, postTitle, status, author', 'safe', 'on'=>'search'), );} |
The biggest change will probably happen in our search function. First we'll add the criteria:
|
1
2
3
4
5
6
7
|
$criteria=new CDbCriteria;$criteria->with = "post"; // Make sure you query with the post table.$criteria->compare('t.content',$this->content,true);$criteria->compare('t.status',$this->status);$criteria->compare('t.author',$this->author,true);$criteria->compare('post.title', $this->postTitle,true); |
Next we'll add the sorting:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
$sort = new CSort();$sort->attributes = array( 'defaultOrder'=>'t.create_time DESC', 'content'=>array( 'asc'=>'t.content', 'desc'=>'t.content desc', ), 'status'=>array( 'asc'=>'t.status', 'desc'=>'t.status desc', ), 'author'=>array( 'asc'=>'t.author', 'desc'=>'t.author desc', ), 'postTitle'=>array( 'asc'=>'post.title', 'desc'=>'post.title desc', ),); |
You might have noticed that I'm using the full 'tablename'.'columnname' syntax. I'm doing this because this way we'll avoid mysql throwing a 'column is ambigious error'.
To make sure this all works, we have to pass on the CSort instance and the CDbCriteria instance to the CActiveDataProvider like this:
|
1
2
3
4
|
return new CActiveDataProvider('Comment', array( 'criteria'=>$criteria, 'sort'=>$sort)); |
Now all we have to do is change our view so that it takes the property name inside the CGridView and we're done:
|
1
2
3
4
5
6
|
'columns'=>array( 'content', 'postTitle', 'status', 'author',), |
This should do the trick. Feel free to share this with all your friends!
The blog demo can be found in the Yii download which can be found here.
