1. Lazy Loading
2. Eager Loading (Single Query)
3. Eager Loading (Multiple Query)
4. Which Loading Approach?
5. Together or Not Together, that is the question
6. Notice

It's well known that there are Lazy Loading approach and Eager Loading approach in the relational query. But it's important to note that you should distinguish 2 different modes in the Eager Loading in Yii 1.1.x.

1. Lazy Loading
2. Eager Loading
   1. Single Query
   2. Multiple Query

So there are virtually 3 approaches.

And you are supposed to control the loading approach by specifying with and together.

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

• An Author HAS_MANY Posts

/* Author.php */
public function relations()
{
　　　　return array(
　　　　　　　　'posts' => array(self::HAS_MANY, 'Post', 'author_id'),
　　　　);
}

• A Post BELONGS_TO an Author

/* Post.php */
public function relations()
{
　　　　return array(
　　　　　　　　'author' => array(self::BELONGS_TO, 'Author', 'author_id'),
　　　　);
}

Lazy Loading ¶

The following is an example of lazy loading.

$authors = Author::model()->findAll();  // 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::model()->findAll() 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 (Single Query) ¶

The following is an example of eager loading in single query mode.

$authors = Author::model()->with('posts')->findAll();  // 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::model()->with('posts')->findAll() 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.

There is only 1 query here.

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

Note that the result set of this query can be quite large. When there are 100 authors and every author has 5 posts, then the result set will contain 500 rows.

Yii will loop through the result set to populate the array of Authors and their Posts in a single shot.

Eager Loading (Multiple Query) ¶

The following is an example of eager loading in multiple query mode.

$authors = Author::model()->with('posts')->findAll(array('limit' => 10));
    // fetches 10 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>";
}

In the above, Author::model()->with('posts')->findAll(array('limit' => 10)) fetches 10 authors with their posts. And there will be no extra queries when you access $author->posts. It looks almost the same with the former example. But there's a big difference here.

It uses 2 queries in findAll() to fetch the authors with their posts.

Because Yii can not use a single query joining the post table like the following:

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

The sql above will fetch 10 rows, but it doesn't mean it fetches 10 authors. When every author has 5 posts, then the result set of the query above will contain only 2 authors.

So Yii has to execute 2 queries like the following:

[sql]
SELECT * FROM author LIMIT 10;
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.

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

Which Loading Approach? ¶

OK. Then, which loading approach will Yii use for a relational query?

This is the rule:

• not specifying with => Lazy Loading
• specifying with => Eager Loading
  • not specifying together
    • BELONGS_TO or HAS_ONE => Single Query
    • HAS_MANY or MANY_MANY
      • with LIMIT and/or OFFSET => Multiple Query
      • without LIMIT or OFFSET => Single Query
  • together is false => Multiple Query
  • together is true => Single Query

By using with and together appropriately, you can select the loading approach as you want.

Remember that there's no simple answer which approach you should take for a particular scenario. Sometimes the lazy loading might be the most efficient one. It depends on what data you have and what you want to do.

Together or Not Together, that is the question ¶

When you are dealing with a HAS_MANY or MANY_MANY relation, especially with CGridView or CListView, you are very likely to face the dilemma.

• If you want to filter the result by an attribute in the related model, you have to use the single query eager loading, otherwise you will get column not found error.
• But if you want to limit the number of main model correctly, you have to use the multiple query eager loading (or lazy loading).

What if you want both of them at the same time? There seems to be no easy answer to it.

The following are the examples of the effort to answer it.

• wiki - Drills : Search by a HAS_MANY relation
• wiki - Displaying, sorting and filtering HasMany & ManyMany relations in CGridView

Notice ¶

"Single Query" and "Multiple Query" are the terms that I molded myself. They are not from the official documentation. But bearing them in mind, you will find things get easier to comprehend.

And one more thing ... this article is Yii 1.1.x specific. Yii 2 doesn't have Single Query Eager Loading.

• wiki - Relational Query - Eager Loading in Yii 2.0