4.7. Eager Fetching

Let's say you fetch a collection of 10 blog records, and attempt to display the author name and a summary datapoint using the lazy-loading feature of the model system:

<?php
/**
 * @var Solar_Sql_Model_Catalog $model
 */

$list = $model->blogs->fetchAll(array(
    'page'   => 1,
    'paging' => 10,
));

foreach ($list as $item) {
    echo "Blog ID# " . htmlspecialchars($item->id)
       . " by "      . htmlspecialchars($item->author->name)
       . " has "     . htmlspecialchars($item->summary->comment_count)
       . " comments<br />";
}

This will result in a massive 21 queries. The first query is for the original list of blog entries. However, when we get to the loop, we issue one query to get that blog entry's author, and another query to get that blog entry's summary record. That's two queries per loop iteration over the ten blog entries, for an additional 20 queries. This behavior is called the "N+1" problem, where "1" is the original query, and the "N" is all the extra individual lazy-load queries.

Lazy-loading related data is convenient, but if you know in advance that you're going to need certain related data, you should eager-fetch it to reduce the number of queries involved. To perform an eager fetch, specify the names of the relateds you want to retrieve:

<?php
/**
 * @var Solar_Sql_Model_Catalog $model
 */

$list = $model->blogs->fetchAll(array(
    'page'   => 1,
    'paging' => 10,
    'eager'  => array('author', 'summary'), // eager fetch
));

foreach ($list as $item) {
    echo "Blog ID# " . htmlspecialchars($item->id)
       . " by "      . htmlspecialchars($item->author->name)
       . " has "     . htmlspecialchars($item->summary->comment_count)
       . " comments<br />";
}

Now only one query will be issued. Because author and summary are to-one relationships, the Solar model system will LEFT JOIN them to the blogs query, merging the data into a single result set at the database, and then emerge the related data into their own objects in PHP.

While to-one relateds are joined into the original result set, to-many relateds generate one additional query per relationship. This is *not* the same as N+1; no matter how many records are in the original result set, there will be only one additional query for each to-many related. For example, to also get the tags and comments on the blog listing, you would do the following:

<?php
/**
 * @var Solar_Sql_Model_Catalog $model
 */

$list = $model->blogs->fetchAll(array(
    'page'   => 1,
    'paging' => 10,
    'eager'  => array('author', 'summary', 'comments', 'tags'),
));

This generates a total of three queries: one for the blogs listing with author and summary joined in, one for all comments on the blog result sets, and one for all tags on the blog result set. (If we had done this by lazy-loading each of the relateds in a loop across the blogs result set, there would have been a total of 41 queries: the original blog query, plus 10 each for the author, summary, comments, and tags.)

4.7.1. Setting Conditions for Eager Fetches

At times you may want to have additional conditions on the related model to narrow down your query. For example, let's say you want fetch all tags that belong to a certain blog. You can achieve this by setting the eager-fetch more explicitly:

<?php
/**
 * @var Solar_Sql_Model_Catalog $model
 */

$list = $model->tags>fetchAll(array(
    'page'   => 1,
    'paging' => 10,
    'eager'  => array(
        'blogs' => array(
            'join_type' => 'inner',
            'conditions' => array(
                'blog_id = 1',   // refers to taggings.blog_id in this case
                //'condition2',
                //...
            )
        )
    )
));


Local