YII - 加载所有has_many记录接受条件的所有父记录

Question

In the below code this will load all author with one of its posts that has post.title = 'search'

// 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 . "
";
    foreach($author->posts as $post)
    {
        echo "Post = " . $post->title . "
";
    }
}

But what I need is only load those authors whose all posts accepts the condition. I am using pagination here so I would like to do it one find statement.

I browsed a lot on this but what can find is eager loading and lazy loading of HAS_MANY records but what I need is different.

Can anyone help on this please?

Answer 1

This will provide authors with all posts satisfying the condition

// query criteria
$criteria = new CDbCriteria();
// with Post model
$criteria->with = array('posts');
// compare title
$criteria->compare('posts.title', $searchWord, true);

$criteria->select = ['*',new CDbExpression('(SELECT COUNT(id) FROM posts WHERE author_id = t.id) AS all_posts'),new CDbExpression('COUNT(posts.id) AS post_count')];

$criteria->group = 't.id';

$criteria->distinct = true;

$criteria->having = 'all_posts = post_count';

// find all authors
$authors = Author::model()->findAll($criteria);
// show all authors and his/her posts
foreach($authors as $author)
{
    echo "Author = " . $author->name . "
";
    foreach($author->posts as $post)
    {
        echo "Post = " . $post->title . "
";
    }
}