As far as I understand, searching by HAS_MANY or MANY_MANY relation in CGridView or in CListView ca be very challenging when your needs are a little bit complicated. We can do searching with those relations using ‘with’ and ‘together’. But in some cases we can not expect proper results if we also want to paginate the results in a grid or in a list.
This post can be very long. I would like to begin with the very basic. Please be patient.
'tags' => self::MANY_MANY('Tag', 'tag_connector(Article_id, Tag_id)'),
I assume the relation above in the following. But it is the same with HAS_MANY relation.
$articles = Article::model()->findAll($condition_a);
foreach($articels as $article)
foreach($article->tags as $tag)
$tag_text = implode(',', $tags);
echo '<p>' . $article->title . ' : tags = ' . $tag_text . '</p>';
In the above, 1 + N queries will be executed.
The first one is for the articles. And the rest are for tags that are attached to the articles. Those additional queries are executed by accessing "$article->tags".
This is called "lazy loading" approach.
Note that $condition_a can only refer to the columns in Article table.
You may also do it like this with "eager loading" approach:
$articles = Article::model()->with('tags')->findAll($condition_<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />;
foreach($articels as $article)
... // same as above
This will execute just one query that retrieve the articles with their tags all at once.
And $condition_b can have some condition regarding the columns in Tag table, because the query joins Tag table with Article table.
Search by MANY_MANY Relation with Eager Loading
So you can do something like this:
$search_tag = 'php';
$articles = Article::model()->with('tags')->
findAll('tags.tag = :tag', array(':tag' => $search_tag));
This will retrieve all the articles that have a tag named ‘php’.
Well, sounds promising, doesn’t it?
There’s one thing you have to note. All the article objects retrived in this way don’t have any tags other than ‘php’. For example, even if an article named ‘yii framework’ has 2 tags of ‘php’ and ‘framework’, the array of $model->tags contains only one tag of ‘php’, while it will contain ‘php’ and ‘framework’ in lazy loading.
Article::search() method for CActiveDataProvider
Now, let’s modify Article::search() method for CGridView:
public function search()
$criteria = new CDbCriteria;
$criteria->with = 'tags';
$criteria->together = true; // ** IMPORTANT **
$criteria->compare('t.id', $this->id); // Article ID
$criteria->compare('t.title', $this->title, true); // Article Title
$criteria->compare('tags.tag', $this->search_tag); // Tag
return new CActiveDataProvider(get_class($this), array(
'criteria' => $criteria,
'pagination' => array(
'pageSize' => 10,
"$criteria->together = true" is important in this context.
Usually “with” is enough to apply the eager loading, but it will be ignored when the pagination is used (i.e. “OFFSET” and “LIMIT” is used). So we set “together” to true to enforce the eager loading, otherwise comparing of ‘tags.tag’ to $this->search_tag will cause an error.
You have to add ‘search_tag’ virtual property to Article model and use it in your view.
Please refer to the following wiki article for detail. It’s a must read.
Searching and sorting by related model in CGridView
Now, here comes a very difficult problem.
What if your ‘$search_tag’ is empty?
Then “$criteria->compare(‘tags.tag’, $this->search_tag)” will be ignored and all the articles will be listed in the grid.
But you will notice that the pagination is out of order. For example, page #1 may contain only 5 rows instead of 10.
Or if you compare ‘tags.tag’ and ‘search_tag’ with the 3rd parameter set to true, meaning using ‘LIKE’ clause, then the same problem will occur.
Every condition that will result in 2 or more applicable tags will cause the same problem.
This is a side effect of eager loading. The number of Article objects can be lesser than that of retrieved db records in eager loading, while they are assured to be the same in lazy loading. We can not work around it, because "LIMIT" clause is for db records, not for AR objects.
In short, we have to do eager loading for search and need lazy loading for proper pagination.
So, what should we do?
It all depends on the needs and the limitations of the app. There’s no single clear cut solution for it, IMO.
P.S. to all the fellows
Please let me know if I’m wrong in this post.