1. Lazy Loading
2. Eager Loading
3. Why not use JOIN?
4. What join and joinWith Do?
5. Related Articles

It's well known that there are Lazy Loading approach and Eager Loading approach in the relational query. But it's very important to know how the Eager Loading approach works in Yii 2. It has changed drastically from that of Yii 1.1, and there are common misunderstandings about it in the community.

In the following sections, we assume an example of 1:N relation like this:

• An Author has_many Posts

/* Author.php */
public function getPosts()
{
    return $this->hasMany(Post::className(), ['author_id' => 'id']);
}

• A Post has_one Author

/* Post.php */
public function getAuthor()
{
    return $this->hasOne(Author::className(), ['id' => 'author_id']);
}

Lazy Loading ¶

The following is an example of lazy loading.

$authors = Author::find()->all();  // fetches only the authors
foreach($authors as $author) {
    echo "<h2>Author : " . $author->name . "</h2>";
    echo "<ul>";
    foreach($author->posts as $post) {  // fetches the author's posts here
        echo "<li>" . $post->title . "</li>";
    }
    echo "</ul>";
}

Without specifying 'with', Author::find()->all() fetches only the authors. The posts of each author will be fetched later when they are accessed with $author->posts.

There are 1 + N queries here: 1 for fetching the authors, and N for fetching the authors' posts.

The main query is something like this:

[sql]
SELECT * FROM author;

Yii will populate the array of Authors using the result set of this query. Author's posts are not yet populated at this point.

And the following N queries for the related Post model should be something like this:

[sql]
SELECT * FROM post WHERE author_id = X;

Where X refers to the id of the author.

Using the result set of this query, Yii will populate the array of Posts for the individual Author.

Eager Loading ¶

The following is an example of eager loading.

$authors = Author::find()->with('posts')->all();  // fetches the authors with their posts
foreach($authors as $author) {
    echo "<h2>Author : " . $author->name . "</h2>";
    echo "<ul>";
    foreach($author->posts as $post) {  // no query executed here
        echo "<li>" . $post->title . "</li>";
    }
    echo "</ul>";
}

By specifying 'with', Author::find()->with('posts')->all() fetches the authors with their posts at once. There will be no extra queries when you access $author->posts, because the posts of each author are already populated.

In order to fetch both Authors and their Posts, Yii uses two queries.

[sql]
SELECT * FROM author;
SELECT * FROM post WHERE author_id IN (1, 3, 7, 10, ... X);

Where (1, 3, 7, 10, ... X) is an array of author's id fetched in the first query.

Looping through the 2 result sets, Yii will populate the array of Authors with their Posts. The 1st query is used to populate the array of main models (Author), while the 2nd query is used to populate the related models (Posts).

Note that the total number of queries is 2 because we include only one relation here. It can be 3 or more when you have included 2 or more relations.

Why not use JOIN? ¶

You may want to ask "Why two queries ? You just need one query using JOIN like the following".

[sql]
SELECT * FROM author JOIN post ON post.author_id = author.id;

"It's more effective, isn't it?"

Yes, it looks more effective, and actually it is sometimes. In fact, Yii 1.1 had this single query approach using JOIN, along with the multiple query approach without JOIN.

But the single query approach has a fatal drawback when you want to use LIMIT and/or OFFSET.

Imagine you wanted to list 10 authors with their posts. You would write like this:

$authors = Author::find()->with('posts')->limit(10)->all();

Yii 2 will then execute the following set of queries:

[sql]
SELECT * FROM author LIMIT 10;
SELECT * FROM post WHERE author_id IN (1, 3, 7, 10, ... X);

This will work as expected.

But the following query can't be used here:

[sql]
SELECT * FROM author JOIN post ON post.author_id = author.id LIMIT 10;

Although it will fetch 10 rows, it doesn't mean 10 authors since every author can have multiple posts.

Among other major reasons that the developers have considered is that a relation between relational db and NOSQL db can be easily implemented with the separated query approach. And Yii 2 has discarded the single query approach for the Eager Loading. Please remember, in Yii 2, the related models are always fetched with a separated query.

What join and joinWith Do? ¶

Along with 'with', we also have 'join', 'joinWith' and the likes which we can use to construct the query. They are useful when you want to filter the result by an attribute of the related model.

For example, when you want to list authors who have at least one post with a title containing some key word, you can write like the following using 'leftJoin':

$authors = Author::find()
    ->leftJoin('post', ['post.author_id' => 'author.id']) // the table name and the condition for 'ON'
    ->where(['like', 'post.title', $keyword])
    ->all();
foreach($authors as $author) {
    echo "<h2>Author : " . $author->name . "</h2>";
    echo "<ul>";
    foreach($author->posts as $post) {  // fetches the author's posts here
        echo "<li>" . $post->title . "</li>";
    }
    echo "</ul>";
}

Here you have to specify the table name and the condition for 'ON' explicitly. And using 'join' and the likes will result in lazy loading, if you haven't specified 'with'.

So, probably you may want to use 'joinWith' like the following:

$authors = Author::find()
    ->joinWith('posts') // the relation name
    ->where(['like', 'post.title', $keyword])
    ->all();
foreach($authors as $author) {
    echo "<h2>Author : " . $author->name . "</h2>";
    echo "<ul>";
    foreach($author->posts as $post) {  // no query executed here
        echo "<li>" . $post->title . "</li>";
    }
    echo "</ul>";
}

'joinWith' doesn't require the table name and the condition for 'ON'. You just have to specify the relation name, and it will result in eager loading by default.

In both of them, the related table is joined in the 1st query of the eager loading. The result set of it may include some columns from the related table, but they are not used to populate the related models. The related models will only be fetched by the subsequent 2nd query without JOIN. Remember, again, the related models are always fetched with a separated query.

Well, then, what are 'join' and 'joinWith' for? They are for filtering and/or sorting the main models by a column in the related table.

Related Articles ¶

You may find these related articles helpful.

• wiki - Relational Query - Lazy Loading and Eager Loading in Yii 1.1 / with and together
• wiki - Drills : Search by a HAS_MANY relation in Yii 1.1
• wiki - Drills : Search by a HAS_MANY relation in Yii 2.0

Discarding of Single Query approach in Yii 2 may be controversial, but I hope you understand by reading these articles that it has made Yii 2 AR cleaner and more effective than that of Yii 1 as a whole.