Eliminating N+1 Queries in Phalcon: Eager Loading with QueryBuilder and Relationships

Problem: The N+1 Query Spiral in PHP MVC

When a controller fetches a list of Users and then loops over each user to pull Posts and Comments, the ORM executes a separate SQL statement for every child record. In a typical Phalcon 5 application this can translate into hundreds or thousands of round‑trips, severely hurting response time and database load.

Eager Loading in Phalcon

Phalcon’s ORM does not provide a single option that automatically nests related models. Instead, you can:

• Define relationships in the model classes (e.g. hasMany, belongsTo).
  These relationships allow the ORM to load related records in a second query when you call $user->getPosts().
• Use Phalcon\Mvc\Model\QueryBuilder to build a single joined query that pulls parent and child columns in one statement.

Both approaches reduce the number of round‑trips, but they behave differently. The QueryBuilder join returns a flat result set; you must post‑process it to build a nested structure. The relationship approach keeps the result in separate model objects and performs an additional query per parent, but you can use getRelated() with hydrate to fetch all children in one go.

Using QueryBuilder with Joins

Below is a minimal example that pulls users, their posts, and each post’s comments in a single SQL statement. The code runs on a Phalcon 5+ application and assumes the following model relationships:

• User hasMany Posts via user_id.
• Post hasMany Comments via post_id.

use Phalcon\Mvc\Model\QueryBuilder;

$builder = new QueryBuilder();
$builder
    ->from(['u' => 'Users'])
    ->columns(['u.id', 'u.name',
              'p.id as post_id', 'p.title',
              'c.id as comment_id', 'c.content'])
    ->innerJoin('Posts', 'p.user_id = u.id', 'p')
    ->leftJoin('Comments', 'c.post_id = p.id', 'c')
    ->orderBy('u.name');

$results = $builder->getQuery()->execute();

Because the query returns a flat set of rows, you’ll need to group the data into a nested array yourself. One common pattern is to iterate over the result set and build a tree keyed by user and post IDs:

$data = [];
foreach ($results as $row) {
    $uid = $row->id;
    if (!isset($data[$uid])) {
        $data[$uid] = [
            'id'    => $uid,
            'name'  => $row->name,
            'posts' => []
        ];
    }
    $pid = $row->post_id;
    if ($pid && !isset($data[$uid]['posts'][$pid])) {
        $data[$uid]['posts'][$pid] = [
            'id'       => $pid,
            'title'    => $row->title,
            'comments' => []
        ];
    }
    $cid = $row->comment_id;
    if ($cid) {
        $data[$uid]['posts'][$pid]['comments'][$cid] = [
            'id'      => $cid,
            'content' => $row->content
        ];
    }
}

After the loop, $data contains a nested structure similar to:

[0 => ['id' => 1, 'name' => 'Alice', 'posts' => [
    10 => ['id' => 10, 'title' => 'Hello', 'comments' => [
        100 => ['id' => 100, 'content' => 'Nice post!']
    ]]
]]]

Fetching Related Models with getRelated()

When you prefer to keep each model separate, you can still avoid the N+1 pattern by using getRelated() with the hydrate option. The following example loads all users and then pre‑fetches all related posts and comments in two additional queries:

$users = User::find(['columns' => 'id, name']);

// The following call loads all posts for the fetched users in a single query.
$users->getRelated('Posts', ['columns' => 'id, user_id, title']);

// For each user, load comments for their posts in a single query.
foreach ($users as $user) {
    $user->getRelated('Posts')->getRelated('Comments', ['columns' => 'id, post_id, content']);
}

After these calls, accessing $user->Posts or $post->Comments will not hit the database again.

Profiling to Verify the Gain

Phalcon’s Db\Profiler can be attached to the database connection via the eventsManager. The following snippet shows how to capture the number of executed queries for a controller action:

use Phalcon\Db\Adapter\Pdo\Mysql as DbAdapter;
use Phalcon\Db\Profiler as DbProfiler;
use Phalcon\Events\Manager as EventsManager;

$profiler = new DbProfiler();
$eventsManager = new EventsManager();
$eventsManager->attach('db', $profiler);

$connection = new DbAdapter([
    'host'     => '127.0.0.1',
    'username' => 'root',
    'password' => '',
    'dbname'   => 'mydb'
]);
$connection->setEventsManager($eventsManager);

// Run the eager‑loaded query
$builder = new QueryBuilder();
$builder->setDI($this->getDI());
$builder->setConnection($connection);
$builder->from(['u' => 'Users']);
$builder->innerJoin('Posts', 'p.user_id = u.id', 'p');
$builder->leftJoin('Comments', 'c.post_id = p.id', 'c');
$builder->columns(['u.id', 'u.name', 'p.id as post_id', 'p.title', 'c.id as comment_id', 'c.content']);
$builder->getQuery()->execute();

// After execution, inspect the profiler
$queries = $profiler->getStatements();
echo "Total queries: " . count($queries) . PHP_EOL;
foreach ($queries as $q) {
    echo $q->getSqlStatement() . PHP_EOL;
}

Run this script once with a simple find() loop that triggers the N+1 pattern, then again with the eager‑loaded query. The difference in count($queries) is the most straightforward way to confirm the reduction.

Trade‑offs and Limitations

• Duplicate Rows – A join that pulls parent, child, and grandchild columns will produce a row for every combination, which can inflate memory usage and require deduplication in PHP.
• Complexity of Grouping – Manual grouping logic can become error‑prone for deeply nested relationships. Consider using a dedicated library or writing reusable helper functions.
• Driver & Schema – Large joins may be slow on certain database engines or when the tables lack proper indexes. Test the query plan with EXPLAIN.
• Cache Invalidation – If you cache the flattened result, remember that updates to child tables need a cache refresh to stay consistent.

Practical Checklist

1. Identify the N+1 pattern by inspecting logs or using a profiler.
2. Define clear relationships in each model.
3. Choose between QueryBuilder joins or getRelated() pre‑fetching based on the size of the result set.
4. Group flat results into nested arrays if you need a tree structure.
5. Profile before and after the change; verify query count and execution time.
6. Monitor memory usage with memory_get_peak_usage() to ensure the join does not exceed limits.
7. Document the chosen strategy in the codebase.

Conclusion

Phalcon’s ORM gives you two powerful tools to break the N+1 cycle: relationship pre‑fetching and the QueryBuilder’s join capability. While the raw SQL may look more complex, the runtime cost drops dramatically. As always, measure the impact in your own environment, watch for duplicate rows, and keep an eye on memory consumption. Once you’re confident, the eager‑loaded route will be both faster and easier to maintain.