Drills : Search by a HAS_MANY relation in Yii 1.1

Tutorials

1.1

search,has_many, search, CActiveRecord

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.

## RelationThink 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 modelclass Author extends CActiveRecord
{
    ...     public function relations() {
 
    {
 
        return array(             'posts' => array(self::HAS_MANY, 'Post', 'author_id'); );
 
}
 
        );
 
    }
 
    ... ```
```php

/**
* Post modelclass 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";
 
}
 
}
 
}
 
```
 
 
> Info|Notice : Unfortunately, this trick seems to work only with MySQL, which has an extended implementation of GROUP BY.
 
 
Now, here is the last task.
 
 
## Task #6
 
 
**Show top 5 authors in the order of name with his/her relevant posts who has at least one post that has a certain word in post title**
 
 
Probably the filtering in lazy loading should be the only answer. I cant't think of another solution. 
 
 
### 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(
 
'condition' => 'title LIKE :search_word',
 
'params' => array(
 
':search_word' => '%' . $searchWord . '%',
 
),
 
)
 
);
 
foreach($filteredPosts as $post)
 
{
 
echo "Post = " . $post->title . "\n";
 
}
 
}
 
}
 
```
 
 
It might not looks very elegant, but I think it's a decent solution without any hacky tricks.
 
 
The key point of the answer is that you can dynamically define the query options of the relation in lazy loading.
 
 
The guide says:
 
 
> Info|Guide : Dynamic query options can also be used when using the lazy loading approach to perform relational query. To do so, we should call a method whose name is the same as the relation name and pass the dynamic query options as the method parameter. 
 
 
## Conclusion and Notice
 
 
Well, we've managed to accomplish all the tasks without getting lost.
 
 
It has been a little surprise to me, because I didn't expect it. Originally, my intention for this article was just to show the dilemma in the search by a HAS_MANY and the reason for it.
 
 
CActiveRecord of Yii has been more powerful than I have imagined.
 
 
### CActiveDataProvider
 
 
Although we didn't take up CActiveDataProvider here, the basic concepts we discussed are also applicable to it. You should note that LIMIT is usually there to confuse you, because CActiveDataProvider is normally used with CPagination.
 
 
### The Guide
 
 
"The guide" in this article refers to "The Definitive Guide to Yii", particularly the section of "Relational Active Record".
 
 
[The Definitive Guide to Yii : Relational Active Record](http://www.yiiframework.com/doc/guide/1.1/en/database.arr)
 
 
It's not quite easy to understand all the content of it, esspecially when you are new to Yii. But you **MUST** read it. I can also recommend to those people who feel at home with Yii to read it over once in a while. It's worth the time.
 
 
## UPDATE: Using a dedicated relation for searching
 
 
So, this is **a very important** update to this article.
 
 
By specifying a dedicated relation for searching, we can join the same table independently for filtering and fetching data.
 
 
For instance, the answer to the task #4 could be written like the following:
 
 
### 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";
 
}
 
}
 
}
 
```
 
 
The difference between the original answer and the optimized one lies in the **performance**.
 
 
There were **1 + N** queries executed in the original answer, because every `$author->posts` would trigger a query to fetch the posts.
 
 
But there are only **2** queries here. There's no query executed for each `$author->posts`, because all the posts have been fetched in `findAll`. Yes, findAll executes 2 queries: one for the relations with 'together', and the other for those without 'together'. (For filtering in the 2nd query, Yii will use the primary keys fetched in the 1st query in `IN` condition.)
 
 
Likewise, the answers to the task #5 and #6 could be optimized like the following:
 
 
### 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";
 
}
 
}
 
}
 
```
 
        ),
 
    );
 
    // 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";
 
        }
 
    }
 
}
 
```
 
 
> Info|Notice : Unfortunately, this trick seems to work only with MySQL, which has an extended implementation of GROUP BY.
 
 
Now, here is the last task.
 
 
## Task #6
 
 
**Show top 5 authors in the order of name with his/her relevant posts who has at least one post that has a certain word in post title**
 
 
Probably the filtering in lazy loading should be the only answer. I cant't think of another solution. 
 
 
### 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(
 
                'condition' => 'title LIKE :search_word',
 
                'params' => array(
 
                    ':search_word' => '%' . $searchWord . '%',
 
                ),
 
            )
 
        );
 
        foreach($filteredPosts as $post)
 
        {
 
            echo "Post = " . $post->title . "\n";
 
        }
 
    }
 
}
 
```
 
 
It might not looks very elegant, but I think it's a decent solution without any hacky tricks.
 
 
The key point of the answer is that you can dynamically define the query options of the relation in lazy loading.
 
 
The guide says:
 
 
> Info|Guide : Dynamic query options can also be used when using the lazy loading approach to perform relational query. To do so, we should call a method whose name is the same as the relation name and pass the dynamic query options as the method parameter. 
 
 
## Conclusion and Notice
 
 
Well, we've managed to accomplish all the tasks without getting lost.
 
 
It has been a little surprise to me, because I didn't expect it. Originally, my intention for this article was just to show the dilemma in the search by a HAS_MANY and the reason for it.
 
 
CActiveRecord of Yii has been more powerful than I have imagined.
 
 
### CActiveDataProvider
 
 
Although we didn't take up CActiveDataProvider here, the basic concepts we discussed are also applicable to it. You should note that LIMIT is usually there to confuse you, because CActiveDataProvider is normally used with CPagination.
 
 
### The Guide
 
 
"The guide" in this article refers to "The Definitive Guide to Yii", particularly the section of "Relational Active Record".
 
 
[The Definitive Guide to Yii : Relational Active Record](http://www.yiiframework.com/doc/guide/1.1/en/database.arr)
 
 
It's not quite easy to understand all the content of it, esspecially when you are new to Yii. But you **MUST** read it. I can also recommend to those people who feel at home with Yii to read it over once in a while. It's worth the time.
 
 
## UPDATE: Using a dedicated relation for searching
 
 
So, this is **a very important** update to this article.
 
 
By specifying a dedicated relation for searching, we can join the same table independently for filtering and fetching data.
 
 
For instance, the answer to the task #4 could be written like the following:
 
 
### 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";
 
        }
 
    }
 
}
 
```
 
 
The difference between the original answer and the optimized one lies in the **performance**.
 
 
There were **1 + N** queries executed in the original answer, because every `$author->posts` would trigger a query to fetch the posts.
 
 
But there are only **2** queries here. There's no query executed for each `$author->posts`, because all the posts have been fetched in `findAll`. Yes, findAll executes 2 queries: one for the relations with 'together', and the other for those without 'together'. (For filtering in the 2nd query, Yii will use the primary keys fetched in the 1st query in `IN` condition.)
 
 
Likewise, the answers to the task #5 and #6 could be optimized like the following:
 
 
### 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";
 
        }
 
    }
 
}
 
```