Yii 1.1: Relational Query - Lazy Loading and Eager Loading / with and together

20 followers

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:

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:

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.

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:

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:

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.

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.

Total 10 comments

#18490 report it
samarhaider at 2014/11/06 03:44am
Great Article on performance

Its very helpful for performance optimization Thanks

#15950 report it
softark at 2014/01/02 07:49pm
RE: maybe execute more than double query!

Thanks Nabi. Updated the article according to your suggestion.

#15949 report it
Nabi at 2014/01/02 04:00pm
maybe execute more than double query!

in Eager Loading mode with limit not always execute double query! if you have more relations with MANY_MANY or HAS_MANY, for each more relation, will be execute a new one other query.

#14457 report it
softark at 2013/08/13 05:21am
RE: Translation

Thank you. It's my honor that you have translated the article.

I think that the SQL profiling is the most reliable way to check the behavior of Yii AR.

#14446 report it
Shahcheraghean at 2013/08/12 11:03am
Translate the post

OK, So Thank`s again. I translate this article in my language here

And, when I have a query, in any kind of model`s relations, How can I find out which approach Yii use - exactly i mean in BELOGNS_TO, HAS_ONE and together or not?

#14445 report it
softark at 2013/08/12 10:43am
@Shahcheraghean

Thank you for your comment. I appreciate it very much.

Well, I understand your request. But I didn't think that I could add any detailed examples that are ready to be applied in real-life projects without losing simplicity and clarity. I rather wanted this article to be as simple and short as it could be.

#14433 report it
Shahcheraghean at 2013/08/11 10:58am
Grate description

Thank`s for your description. It was so useful. But, May I ask you to make some more example for any approach.

#14061 report it
Nisanth thulasi at 2013/07/16 05:42am
Cool

This is really helpfull and should use

#13929 report it
trond at 2013/07/07 02:57pm
Very interesting post

I found this very enlightening.

#13925 report it
farhad2161 at 2013/07/06 11:16am
Great

So cool.

Leave a comment

Please to leave your comment.