Yii Framework Wiki
Difference between
#7 and
#16 of
Drills : Search by a HAS_MANY relation in Yii 2.0
Changes
Title unchanged
Drills : Search by a HAS_MANY relation in Yii 2.0
Category unchanged
Tutorials
Yii version changed
2.0
Tags changed
Content changed
In **Yii 1.1**, we sometimes got lost trying to search by a HAS_MANY relation using CActiveRecord or CActiveDataProvider. The wiki article [Drills : Search by a HAS_MANY relation in Yii 1.1](http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation-in-yii-1-1/) was an effort to offer some practical techniques of searching by a HAS_MANY relation in Yii 1.1.
Now, we have **Yii 2**. Let's examine what can we do with the new ActiveRecord of Yii 2 to solve the same tasks of searching by a HAS_MANY relation.
## Relation
Two entities are sometimes connected with a relation of **1:N**. Or we may say that 1:N is the only possible relation between 2 entities as long as we are in the RDB world. **1:1** relation is just a particular kind of 1:N where N is always assumed to be 1 at the maximum. And **N:N** relation can be considered as a combination of two 1:N relations.
Yii supports this 1:N relation in ActiveRecord as **BELONGS_TO** and **HAS_ONE** relations. 1:N relation seen from the side of N is HAS_ONE, and from the side of 1 it is HAS_MANY.
Now, let's construct an example of 1:N relation.
## Example of HAS_MANY
Think about blog posts and their authors. It's a relation of "An Author has many Posts" and "A Post has an Author" at the same time.
```php
<?php
/**
* Author model
* @property integer $id
* @property string $name the author's name
...
*/
class Author extends \yii\db\ActiveRecord
{
...
/**
* @return \yii\db\ActiveQuery
*/
public function getPosts()
{
return $this->hasMany(Post::className(), ['author_id' => 'id']);
}
...
```
```php
<?php
/**
* Post model
* @property integer $id
* @property integer $author_id FK to the author's id
* @property string $title the title of the post
...
*/
class Post extends \yii\db\ActiveRecord
{
...
/**
* @return \yii\db\ActiveQuery
*/
public function getAuthor()
{
return $this->hasOne(Author::className(), ['id' => 'author_id']);
}
...
```
We are going to solve the possible use cases of search regarding this example.
## Task #1
**Show all posts that has a word in post title**
I want to start with an easy one. Let's retrieve all the posts that has a cirtain word in their title.
```php
<?php
// get all the posts that has a keyword in their title
$posts = Post::find()
->where(['like', 'title', $searchword])
->all();
// display the results
foreach($posts as $post) {
echo "Title = {$post->title}\n";
}
```
Ah, it was too easy. And no relation is involved in this task.
OK, let's move on to the next.
## Task #2
**Show all posts with their authors that has a certain word in post title**
Now we have to retrieve the authors' name, too.
```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();
// display the results
foreach($posts as $post) {
echo "Title = {$post->title}\n";
echo "Author = {$post->author->name}\n";
}
```
Well, you may say "You don't have to do it. Just add a line to echo `$post->author->name` to the code of the 1st task."
Yes, you are right. Post model has `author` relation, so all you have to do to retrieve its Author is just **$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 entire array of Posts plus every one query per each Post for retrieving its author. It will end up in 1+N queries to be executed.
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. It will get all of the related models at once just after retrieving the main models. 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.
In **Yii 1.1**, we sometimes got lost trying to search by a HAS_MANY relation. The wiki article [Drills : Search by a HAS_MANY relation in Yii 1.1](http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation-in-yii-1-1/) was an effort to offer some practical techniques of searching by a HAS_MANY relation in Yii 1.1.
Now, we have **Yii 2**. Let's examine what we can do with the new ActiveRecord of Yii 2 when we have to solve the same tasks of searching by a HAS_MANY relation.
## Relation
Two entities are sometimes connected with a relation of **1:N**. Or we may say that 1:N is the only possible relation between 2 entities as long as we are in the RDB world. **1:1** relation is just a particular kind of 1:N where N is always assumed to be 1 at the maximum. And **N:N** relation can be considered as a combination of two 1:N relations (`N:1-1:N`).
Yii supports this 1:N relation in ActiveRecord as **HAS_ONE** and **HAS_MANY** relations. 1:N relation seen from the side of N is HAS_ONE, and from the side of 1 it is HAS_MANY.
Now, let's construct an example of 1:N relation.
## Example of 1:N relation : Author and Post
Think about blog posts and their authors. It's a relation of "An Author has many Posts" and "A Post has an Author" at the same time. This pair of relations is represented with the following code:
**An Author has many Posts**
```php
/**
* Author model
* @property integer $id
* @property string $name the author's name
*/
class Author extends \yii\db\ActiveRecord
{
/**
* @return \yii\db\ActiveQuery
*/
public function getPosts()
{
return $this->hasMany(Post::className(), ['author_id' => 'id']);
}
```
**A post has one Author**
```php
/**
* Post model
* @property integer $id
* @property integer $author_id FK to the author's id
* @property string $title the title of the post
*/
class Post extends \yii\db\ActiveRecord
{
/**
* @return \yii\db\ActiveQuery
*/
public function getAuthor()
{
return $this->hasOne(Author::className(), ['id' => 'author_id']);
}
```
Note that a relation in Yii 2 is a getter method returning `ActiveQuery`.
We are going to solve the possible use cases of searching with this example.
## Task 1 : Search by Main Attribute, without Relation
**Show all posts with a keyword**
I want to start with a basic task. Let's retrieve all the posts that has a certain keyword in their titles.
```php
// get all the posts that has a keyword in their title
$posts = Post::find()
->where(['like', 'title', $searchword])
->all();
// show the results
foreach($posts as $post) {
echo "Title = {$post->title}\n";
}
```
Ah, it was too easy. And no relation is involved in this task.
OK, let's move on to the next.
## Task 2 : Search by Main Attribute, with Relation
**Show all posts with a keyword, with their authors**
Now we have to retrieve the authors' names, too.
### Lazy Loading
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
// get all the posts that has a keyword in their title
$posts = Post::find()
->where(['like', 'title', $searchword])
->all();
// show the results
foreach($posts as $post) {
echo "Title = {$post->title}\n";
echo "Author = {$post->author->name}\n";
}
```
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.
```sql
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;
...
```
### Eager Loading
The better approach here is the following:
```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.
```sql
SELECT * FROM `post` WHERE `title` LIKE `%searchword%`;
SELECT * FROM `author` WHERE `id` IN (1,2,5,7,...);
```
Just after the 1st query retrieved the main models, the 2nd one will get all of the related models at once. In the above, `(1,2,5,7,...)` refers to the `author_id`s in the result set of the 1st query. 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.
## Task 3 : Search by HAS_ONE Attribute, with Relation
**Show all posts with their authors who have a certain word in name**
You have to search by an attribute in the related model, not in the main model.
```php
// get all the posts that has a keyword in their author's name, with their authors
$posts = Post::find()
->joinWith('author')
->where(['like', 'author.name', $searchword])
->all();
// show the results
foreach($posts as $post) {
echo "Title = {$post->title}\n";
echo "Author = {$post->author->name}\n";
}
```
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. 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.
```sql
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 2.
**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.**
And `joinWith` will cause ActiveRecord to choose the eager loading approach by default, but you may suppress the loading of the related models by specifying the optional parameter `$eagerLoading` as `false`.
You may say "It's just a **HAS_ONE** relation. Yeah, it's simple. I know."
Yes, you are right, definitely. Let's move on to the next task where we will deal with **HAS_MANY**.
## Task 4 : Search by HAS_MANY Attribute, without Relation
**Show all authors who have a post with a keyword in its title**
Now we will retrieve the authors, not the posts.
```php
// get all the authors who have a post that has a keyword in its title
$authors = Author::find()
->joinWith('posts', false) // will not get the related models
->where(['like', 'post.title', $searchword])
->all();
// show the results
foreach($authors as $author) {
echo "Author = {$author->name}\n";
}
```
We join the `posts` relation using `joinWith` because we need `post.title` in the searching, while we set `$eagerLoading` to false because we don't need the posts to be retrieved.
## Task 5 : Search by HAS_MANY Attribute, with Relation
**Show all authors who have a post with a keyword in its title, with all of their posts**
```php
// get all the authors who have a post that has a keyword in its title, with all of their posts
$authors = Author::find()
->joinWith('posts') // will eagerly load the related models
->where(['like', 'post.title', $searchword])
->all();
// show the results
foreach($authors as $author) {
echo "Author = {$author->name}\n";
foreach($author->posts as $post) {
echo "Title = {$post->title}\n";
}
}
```
Don't forget that **the query for the main model and those for the related models are separated**. We add a condition using `post.title` in `where` part in order to filter the main models, but it doesn't affect the loading of the related models. The related models are loaded without any condition.
Well, then, what about the next task? It's a variation of the above.
## Task 6 : Search by HAS_MANY Attribute, with Relevant Relations
**Show all authors who have a post with a keyword in its title, with all of their relevant posts**
In fact, this is a little more confusing than the above.
### Lazy Loading with Customized Relation
One solution is this:
```php
// get all the authors who have a post that has a keyword in its title
$authors = Author::find()
->joinWith('posts', false) // won't get posts
->where(['like', 'post.title', $searchword])
->all();
// show the results
foreach($authors as $author) {
echo "Author = {$author->name}\n";
// get all the posts that have a keyword in its title
$posts = $author->getPosts()->where(['like', 'post.title', $searchword])->all();
foreach($posts as $post) {
echo "Title = {$post->title}\n";
}
}
```
We join the `posts` relation using `joinWith` because we need `post.title` in the searching, while we set `$leagerLoading` to false because we don't want to load the posts at that point. And then we perform the lazy loading of the related posts with an on-the-fly condition for each author afterwards.
### Eager Loading with Customized Relation
You could specify an on-the-fly condition to the relation when you eagerly load the related models:
```php
// get all the authors who have a post that has a keyword in its title
$authors = Author::find()
->joinWith('posts', false) // this is for the main models
->where(['like', 'post.title', $searchword])
->with([ // this is for the related models
'posts' => function($query) use ($searchword) {
$query->where(['like', 'post.title', $searchword]);
},
])
->all();
// show the results
foreach($authors as $author) {
echo "Author = {$author->name}\n";
foreach($posts as $post) {
echo "Title = {$post->title}\n";
}
}
```
Because we are applying the same condition to the relation for searching the main models and that for retrieving related models, we may use a single relation with that condition for both.
```php
// get all the authors who have a post that has a keyword in its title
$authors = Author::find()
->innerJoinWith([
'posts' => function($query) use ($searchword) {
$query->where(['like', 'post.title', $searchword]);
},
])
->all();
// show the results
foreach($authors as $author) {
echo "Author = {$author->name}\n";
foreach($posts as $post) {
echo "Title = {$post->title}\n";
}
}
```
In this way, you can use a customized relation for both the searching of the main models and the loading of the related models.
Let's move on to the next. It's a bit tough, though.
## Task 7 : LIMIT and ORDER
**Show top 5 authors ordered by name who has a post with a key word in its title, with all of their posts**
OK, so we need to add "LIMIT" and "ORDER". Going to try with this one.
### Trial
```php
<?php
$authors = Author::find()
->joinWith('posts')
->where(['like', 'post.title', $searchword])
->orderBy(['author.name' => SORT_ASC])
->limit(5)
->all();
// show the results
foreach($authors as $author) {
echo "Author = {$author->name}\n";
foreach($author->posts as $post) {
echo "Title = {$post->title}\n";
}
}
```
You will notice that there is something strange in the output results. For example, the results may look like the following:
- 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
We could get only 3 authors instead of 5.
Why did it happen? It was because the SQL used to retrieve the main models had been something like the following:
```sql
SELECT * from author
LEFT JOIN post on author.id = post.author_id
WHERE post.title LIKE '%foo%'
ORDER BY author.name
LIMIT 5
```
Notice that `LIMIT 5` is applied to the number of the rows of an virtual table that is the result of joining `author` table with `post` table. The resulted rows may have less unique authors than the limit, because an author may **have many** posts.
In Yii 1.1, we could use **GROUP BY** trick, though it was MySQL specific. It can also be used in Yii 2:
### Solution 1 : GROUP BY
```php
$authors = Author::find()
->joinWith('posts')
->where(['like', 'post.title', $searchword])
->groupBy('author.id')
->orderBy(['author.name' => SORT_ASC])
->limit(5)
->all();
```
In this way we could remove the redundant rows by grouping the results by author's ID.
### Solution 2 : DISTINCT
You may say "Why don't you use **DISTINCT**? It might be cleaner and more compatible than a dirty trick." Seems fairly reasonable. Let's give it a try:
```php
$authors = Author::find()
->distinct()
->joinWith('posts')
->where(['like', 'post.title', $searchword])
->orderBy(['author.name' => SORT_ASC])
->limit(5)
->all();
```
Huh, done!
We are going to the last task, feeling a bit confused by the unexpected easiness.
## Task 8: LIMIT and ORDER, with Relevant Relations
**Show top 5 authors ordered by name who has a post that has a certain word in post title, with all of their relevant posts**
Now it's a simple task. You just have to apply the same filter both for the searching of the main models and the loading the related models.
### Example of a Solution
```php
$authors = Author::find()
->distinct()
->joinWith([
'posts' => function($query) use ($searchword) {
$query->where(['like', 'post.title', $searchword]);
},
])
->orderBy(['author.name' => SORT_ASC])
->limit(5)
->all();
```
Thus we finish the drills in an anticlimactic end.
## Summary
Fantastic, isn't it?
### Reference
Please refer to the database sections of the guide.
- [The Definitive Guide to Yii 2.0](http://www.yiiframework.com/doc-2.0/guide-index.html)
- [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)
14 followers
Viewed: 106 165 times
Version: 2.0
Category: Tutorials
Written by: softark
Last updated by: softark
Created on: Dec 12, 2014
Last updated: 6 years ago
Revisions
View all history-
6 years ago by
softark Revised
-
6 years ago by
softark Added SQL explanation for eager loading
-
8 years ago by
softark typo fixed
-
9 years ago by
jim. Tags added: yii2, search