Yii Framework Wiki
Difference between
#15 and
#20 of
Drills : Search by a HAS_MANY relation in Yii 1.1
Changes
Title unchanged
Drills : Search by a HAS_MANY relation in Yii 1.1
Category unchanged
Tutorials
Yii version changed
1.1
Tags changed
search,has_many, search, CActiveRecord
Content changed
Sometimes we get lost trying to search by a HAS_MANY relation using CActiveRecord or CActiveDataProvider in **Yii 1.1**. This article is a series of drills that try to describe the practical techniques of searching by a HAS_MANY relation.
> Note: Note that this article is Yii 1.1 specific.
> For **Yii 2**, please read the new article - [Drills : Search by a HAS_MANY relation in Yii 2.0](http://www.yiiframework.com/wiki/780/drills-search-by-a-has_many-relation-in-yii-2-0/) that is completelrewritten entirely for Yii 2.0.
## Relation
```php
/**
* Author model
{
...
public function relations()
{
{
return array( 'posts' => array(self::HAS_MANY, 'Post', 'author_id');
);
}
);
}
... ```
```php
/**
* Post model
{
...
public function relations()
{
{
return array( 'author' => array(self::BELONGS_TO, 'Author', 'author_id');
);
}
);
}
...
```
```php
public static function GetPostsByTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// compare title
$criteria->compare('title', $searchWord, true);
// find
$posts = Post::model()->findAll($criteria);
// show
foreach($posts as $post)
{
{
echo "Title = " . $post->title . "\n"; }
}
```
```php
public static function GetPostsWithAuthorByTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// with Author model
$criteria->with = array('author');
// compare title
$criteria->compare('t.title', $searchWord, true);
// find all posts
$posts = Post::model()->findAll($criteria);
// show all posts
foreach($posts as $post)
{
{
echo "Title = " . $post->title . "\n"; echo "Author = " . $post->author->name . "\n";
}
}
```
```php
... // compare Author's name
$criteria->compare('author.name', $searchName, true);
...
```
```php
public static function GetAuthorsByPostTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// with Post model
$criteria->with = array('posts');
// compare title
$criteria->compare('posts.title', $searchWord, true);
// find all authors
$authors = Author::model()->findAll($criteria);
// show all authors
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; }
}
```
```php
public static function GetAuthorsWithPostsByPostTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// with Post model
$criteria->with = array('posts');
// compare title
$criteria->compare('posts.title', $searchWord, true);
// find all authors
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; foreach($author->posts as $post)
{
{
echo "Post = " . $post->title . "\n"; }
}
}
}
```
```php
public static function GetAuthorsWithPostsByPostTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// join Post model (without selecting)
$criteria->with = array(
'posts' => array(
'select' => false,
),
);
),
);
// compare title $criteria->compare('posts.title', $searchWord, true);
// find all authors
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; foreach($author->posts as $post)
{
{
echo "Post = " . $post->title . "\n"; }
}
}
}
```
```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// with Post model
$criteria->with = array('posts');
// compare title
$criteria->compare('posts.title', $searchWord, true);
// order by author name
$criteria->order = 't.name ASC';
// limit to 5 authors
$criteria->limit = 5;
// find all authors
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; foreach($author->posts as $post)
{
{
echo "Post = " . $post->title . "\n"; }
}
}
}
```
```php
... // force to join Post
$criteria->with = array(
'posts' => array(
'together' => true,
),
);
),
);
...
```
~~~
[- search word = **foo]
**
- Author = Andy - Post = Don't use **foo
- Post = Use yoo for **foo
- Author = Ben - Post = **foo** is great
- Post = I love **foo
- Author = Charlie - Post = What's **foo**?
[- end]
~~~
We want to show 5 authors, but the list ends where the count of posts sums up to 5.
```php
... // force to join Post (without selecting)
$criteria->with = array(
'posts' => array(
'together' => true,
'select' => false,
),
);
),
);
... ``` But it still doesn't work. It will show the results like this:~~~
[- search word = **foo]
**
- Author = Andy - Post = Don't use **foo
- Post = Use yoo for **foo
- Post = Don't use bar - Post = Use yar for bar
- Author = Ben
- Post = **foo** is great
- Post = I love **foo
- Post = I also love bar - Author = Charlie - Post = What's **foo**?
- Post = What's bar?
[- end]
~~~
It is because LIMIT is not applied to the primary table, but to the virtually constructed table that is the result of joining. It's no use complaining about this behavior, because that's how the query works in RDB.
```php
... // force to join Post (without selecting)
$criteria->with = array(
'posts' => array(
'together' => true,
'select' => false,
),
);
),
);
... // group by Author's id
$criteria->group = 't.id';
...
```
```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// force to join Post (without selecting)
$criteria->with = array(
'posts' => array(
'together' => true,
'select' => false,
),
);
),
);
// compare title $criteria->compare('posts.title', $searchWord, true);
// group by Author's id
$criteria->group = 't.id';
// order by author name
$criteria->order = 't.name ASC';
// limit to 5 authors
$criteria->limit = 5;
// find all authors
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; foreach($author->posts as $post)
{
{
echo "Post = " . $post->title . "\n"; }
}
}
}
```
```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// force to join Post (without selecting)
$criteria->with = array(
'posts' => array(
'together' => true,
'select' => false,
),
);
),
);
// compare title $criteria->compare('posts.title', $searchWord, true);
// group by Author's id
$criteria->group = 't.id';
// order by author name
$criteria->order = 't.name ASC';
// limit to 5 authors
$criteria->limit = 5;
// find all authors
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; // lazy loading posts with filtering
$filteredPosts = $author->posts(
array(
array(
'condition' => 'title LIKE :search_word', 'params' => array(
':search_word' => '%' . $searchWord . '%',
),
)
);
),
)
);
foreach($filteredPosts as $post) {
{
echo "Post = " . $post->title . "\n"; }
}
}
}
```
```php
public function relations() { return array(
'posts' => array(self::HAS_MANY, 'Post', 'author_id'),
'posts_search' => array(self::HAS_MANY, 'Post', 'author_id'),
);
}
public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
// query criteria
$criteria = new CDbCriteria();
// join Post model (one for fetching data, the other for filtering)
$criteria->with = array(
'posts' => array( // this is for fetching data
'together' => false,
),
),
'posts_search' => array( // this is for filtering 'select' => false,
'together' => true,
),
);
),
);
// compare title $criteria->compare('posts_search.title', $searchWord, true);
// find all authors with his/her posts
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; foreach($author->posts as $post) // no queries executed here !!
{
{
echo "Post = " . $post->title . "\n"; }
}
}
}
```
```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// join Post model (one for fetching data, the other for filtering)
$criteria->with = array(
'posts' => array( // this is for fetching data
'together' => false,
),
),
'posts_search' => array( // this is for filtering 'select' => false,
'together' => true,
),
);
),
);
// compare title $criteria->compare('posts_search.title', $searchWord, true);
// group by Author's id
$criteria->group = 't.id';
// order by author name
$criteria->order = 't.name ASC';
// limit to 5 authors
$criteria->limit = 5;
// find all authors with his/her posts
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; foreach($author->posts as $post) // no queries executed here !!
{
{
echo "Post = " . $post->title . "\n"; }
}
}
}
```
```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) { // query criteria
$criteria = new CDbCriteria();
// join Post model (one for fetching data, the other for filtering)
$criteria->with = array(
'posts' => array( // this is for fetching data
'together' => false,
'condition' => 'posts.title LIKE :search_word',
'params' => array(
':search_word' => '%' . $searchWord . '%',
),
),
),
),
'posts_search' => array( // this is for filtering 'select' => false,
'together' => true,
),
);
),
);
// compare title $criteria->compare('posts.title', $searchWord, true);
// group by Author's id
$criteria->group = 't.id';
// order by author name
$criteria->order = 't.name ASC';
// limit to 5 authors
$criteria->limit = 5;
// find all authors
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
{
echo "Author = " . $author->name . "\n"; foreach($author->posts as $post) // no queries executed here !!
{
{
echo "Post = " . $post->title . "\n"; }
}
} } ```
## Relation
[...]
Think about blog posts and their authors. It's a relation of "An Author has many Posts" and "A Post belongs to an Author" at the same time.```php
/**
* Author model
[...]
class Author extends CActiveRecord{
return array(
}
}
... ```
```php
/**
* Post model
[...]
class Post extends CActiveRecord{
return array(
}
}
...
```
[...]
I want to start with an easy one. Let's retrieve all the posts that has a cirtain word in their title.```php
public static function GetPostsByTitle($searchWord) {
echo "Title = " . $post->title . "\n";
}
```
[...]
Now we have to retrieve the authors' name, too.```php
public static function GetPostsWithAuthorByTitle($searchWord) {
echo "Title = " . $post->title . "\n";
}
```
[...]
So you can write like this when you want to search by Author's name.```php
...
```
[...]
Now we will retrieve the authors, not the posts.```php
public static function GetAuthorsByPostTitle($searchWord) {
echo "Author = " . $author->name . "\n";
}
```
[...]
### Wrong Answer```php
public static function GetAuthorsWithPostsByPostTitle($searchWord) {
echo "Author = " . $author->name . "\n";
echo "Post = " . $post->title . "\n";
}
}
```
[...]
### Answer```php
public static function GetAuthorsWithPostsByPostTitle($searchWord) {
);
);
// compare title
echo "Author = " . $author->name . "\n";
echo "Post = " . $post->title . "\n";
}
}
```
[...]
### Trial #1```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) {
echo "Author = " . $author->name . "\n";
echo "Post = " . $post->title . "\n";
}
}
```
[...]
### Trial #2```php
...
);
);
...
```
[...]
But, alas, you will get the strange output like this:[
- Author = Andy
- Post = Use yoo for **foo
- Author = Ben
- Author = Charlie
~~~
We want to show 5 authors, but the list ends where the count of posts sums up to 5.
[...]
### Trial #3```php
...
);
);
... ``` But it still doesn't work. It will show the results like this:
[
- Author = Andy
- Post = Use yoo for **foo
- Post = Don't use bar
- Post = I also love bar - Author = Charlie
~~~
It is because LIMIT is not applied to the primary table, but to the virtually constructed table that is the result of joining. It's no use complaining about this behavior, because that's how the query works in RDB.
[...]
### Trial #4```php
...
);
);
...
```
[...]
### Answer```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) {
);
);
// compare title
echo "Author = " . $author->name . "\n";
echo "Post = " . $post->title . "\n";
}
}
```
[...]
### Example of Answer```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) {
);
);
// compare title
echo "Author = " . $author->name . "\n";
'condition' => 'title LIKE :search_word',
)
);
)
);
foreach($filteredPosts as $post)
echo "Post = " . $post->title . "\n";
}
}
```
[...]
### Optimized Answer to Task #4```php
public function relations() {
'posts_search' => array( // this is for filtering
);
);
// compare title
echo "Author = " . $author->name . "\n";
echo "Post = " . $post->title . "\n";
}
}
```
[...]
### Optimized Answer to Task #5```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) {
'posts_search' => array( // this is for filtering
);
);
// compare title
echo "Author = " . $author->name . "\n";
echo "Post = " . $post->title . "\n";
}
}
```
[...]
### Optimized Answer to Task #6```php
public static function GetTop5AuthorsWithPostsByPostTitle($searchWord) {
),
),
'posts_search' => array( // this is for filtering
);
);
// compare title
echo "Author = " . $author->name . "\n";
echo "Post = " . $post->title . "\n";
} } ```
27 followers
Viewed: 77 947 times
Version: 1.1
Category: Tutorials
Written by: softark
Last updated by: softark
Created on: Dec 6, 2012
Last updated: 6 years ago
Revisions
View all history-
6 years ago by
softark Changed a tab to 4 spaces
-
6 years ago by
softark Changed markup for query results from "c...
-
6 years ago by
softark Fixed a markup
-
6 years ago by
softark Fixed a markup
-
9 years ago by
softark small change
-
9 years ago by
softark Changed the title, and added a link to t...