Drills : Search by a HAS_MANY relation in Yii 2.0

You are viewing revision #7 of this wiki article.
This version may not be up to date with the latest version.
You may want to view the differences to the latest version.

next (#14) »

  1. Relation
  2. Example of HAS_MANY
  3. Task #1
  4. Task #2

Note: This article is under development.

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 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
/**
 * 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
/**
 * 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
// 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
// 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.

11 0
12 followers
Viewed: 74 396 times
Version: Unknown (update)
Category: Tutorials
Written by: softark
Last updated by: softark
Created on: Dec 12, 2014
Last updated: 9 months ago
Update Article

Revisions

View all history

Related Articles