Drills : Search by a HAS_MANY relation in Yii 2.0

Tutorials

ActiveRecord, has_many, yii2, search

## Task #2

**Show all posts that has a certain word in post title, with their authors**

Now we have to retrieve the authors' names, too. But this is quite easy if you don't care the efficiency. Just adding a line to echo `$post->author->name` would be enough.
 
```php <?php // get all the posts that has a keyword in their title, with their authors $posts = Post::find() ->where(['like', 'title', $searchword])     ->with('author')
 
->all();
// show the results
foreach($posts as $post) {```

Well, you may say "You didn't have to do it. Just adding a line to echo `$post->author->name` to the code of the 1st task would have been enough."
 
 
Yes, you are right. Post model has the `author` relation, so all you have to do to retrieve its Author is just accessing **$post->author**. How convenient is it! It's so-called **lazy loading** approach. But the lazy loading approach has a drawback in this case, because you have to execute one query for retrieving an array of Posts, and every one query per each Post for retrieving its author. It will end up in 1+N queries to be executed. ~~~
 
SELECT * FROM `post` WHERE `title` LIKE `%searchword%`;
 
SELECT * FROM `author` WHERE `id`=1;
 
SELECT * FROM `author` WHERE `id`=2;
 
SELECT * FROM `author` WHERE `id`=5;
 
SELECT * FROM `author` WHERE `id`=7;
 
...
 
~~~
 
 
The better approach here is the following:
 
 
 
```php 
<?php
 
// get all the posts that has a keyword in their title, with their authors
 
$posts = Post::find()
 
    ->where(['like', 'title', $searchword])
 
    ->with('author')
 
    ->all();
 
// show the results
 
foreach($posts as $post) {
 
    echo "Title = {$post->title}\n";
 
    echo "Author = {$post->author->name}\n";
 
}
 
```
 
 
Here we are retrieveing Posts and their Authors at the same time by using **yii\db\ActiveQuery::with()**. It enables you to do the job with only 2 queries. 
 
 
~~~
 
SELECT * FROM `post` WHERE `title` LIKE `%searchword%`;
 
SELECT * FROM `author` WHERE `id` IN (1,2,5,7,...);
 
~~~
 
 
Just after the 1st query has retrieved the main models, the 2nd one will get all of the related models at once.  This is so-called **eager loading** approach.

The eager loading is preferrable in this particular case, because it's more effective. But, you have to note, it is not always so.We **join** the `author` table using **yii\db\ActiveQuery::joinWith()**, because we have to search the main model by an attribute of the related model. Note that we may need to disambiguate the column name with the table name for the parameter of `where`.

The more important thing to note is that the joining tables using `joinWith` will not reduce the number of queries in the eager loading scenario.. You might think that just one query would be enough, but Yii will still use 2 queries, one for the main model and the other for the related model. 
 
 
~~~
 
SELECT * FROM `post` 
 
    LEFT JOIN `author` ON `post`.`author_id` = `author`.`id`
 
    WHERE `author.name` LIKE `%searchword%`;
 
SELECT * FROM `author` WHERE `id` IN (1,2,5,7,...);
 
~~~
 
 
**Yii 2 will always use the separated queries for the main model and the related models.** So, you would not be able to make the code more efficient by modifying `with` to `joinWith` in the answer for the task #12.

**We join the related table using `joinWith` not because we expect good performance, but because we have to access a column in the related table in order to filter the rows in the main table.**- [Query Builder and Query](http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html)
- [Active Record](http://www.yiiframework.com/doc-2.0/guide-db-active-record.html)