[...]
## Example of HAS_MANY
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
{
...
public function relations()
{
{
return array(
'posts' => array(self::HAS_MANY, 'Post', 'author_id');
);
}
);
}
...
```
```php
/**
* Post model[...]
class Post extends CActiveRecord
{
...
public function relations()
{
{
return array(
'author' => array(self::BELONGS_TO, 'Author', 'author_id');
);
}
);
}
...
```[...]
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)
{
// 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";
}
}
```[...]
Now we have to retrieve the authors' name, too.
```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";
}
}
```[...]
So you can write like this when you want to search by Author's name.
```php
...
// compare Author's name
$criteria->compare('author.name', $searchName, true);
...
```[...]
Now we will retrieve the authors, not the posts.
```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";
}
}
```[...]
### Wrong Answer
```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";
}
}
}
}
```[...]
### Answer
```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";
}
}
}
}
```[...]
### Trial #1
```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";
}
}
}
}
```[...]
### Trial #2
```php
...
// force to join Post
$criteria->with = array(
'posts' => array(
'together' => true,
),
);
),
);
...
```[...]
But, alas, you will get the strange output like this:
~~~
[- 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.[...]
### Trial #3
```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.[...]
### Trial #4
```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';
...
```[...]
### Answer
```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";
}
}
}
}
```[...]
### Example of Answer
```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";
}
}
}
}
```[...]
### Optimized Answer to Task #4
```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";
}
}
}
}
```[...]
### Optimized Answer to Task #5
```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";
}
}
}
}
```[...]
### Optimized Answer to Task #6
```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";
}
}
}
}
```
