[...]
* @property integer $name Author's name
...
*/
class Author extends CActiveRecord
{
...
public function relations()
{
{
return array(
'posts' => array(self::HAS_MANY, 'Post', 'author_id');
);
}
);
}
...
```[...]
class Post extends CActiveRecord
{
...
public function relations()
{
{
return array(
'author' => array(self::BELONGS_TO, 'Author', 'author_id');
);
}
);
}
...
```[...]
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";
}
}
```[...]
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);
...
```[...]
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";
}
}
```[...]
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";
}
}
}
}
```[...]
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";
}
}
}
}
```[...]
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,
),
);
),
);
...
```[...]
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.[...]
```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';
...
```[...]
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";
}
}
}
}
```[...]
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";
}
}
}
}
```[...]
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";
}
}
}
}
```[...]
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";
}
}
}
}
```[...]
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";
}
}
}
}
```
