Fixing N+1 Queries in Phalcon with PHQL Joins and Resultset Caching

The N+1 problem in a Phalcon listing endpoint

When a controller returns a collection of BlogPost records and the view accesses $post->getAuthor() for each row, Phalcon's ORM issues one query for the posts and then one additional query per post to fetch the related author. With 50 posts that becomes 51 round‑trips to the database — a classic N+1 pattern that shows up in the query log as a burst of SELECT * FROM authors WHERE id = ? statements.

Eager fetching with PHQL

Phalcon's PHQL (Phalcon Query Language) is a SQL‑like dialect parsed by the framework itself. It lets you express joins, aggregates and bound parameters while still returning fully‑hydrated model instances or lightweight resultsets. Rewriting the lazy loop as a single PHQL join collapses the 51 queries into one.

Before: lazy loading in a loop

$posts = BlogPost::find([
    'order' => 'created_at DESC',
    'limit' => 50
]);

foreach ($posts as $post) {
    $author = $post->getAuthor(); // triggers a separate SELECT per post
    echo $author->name;
}

After: single PHQL join

$phql = "SELECT p.*, a.* 
         FROM BlogPost p 
         JOIN Author a ON p.author_id = a.id 
         ORDER BY p.created_at DESC 
         LIMIT 50";

$results = $this->modelsManager->executeQuery($phql);

foreach ($results as $row) {
    // $row is a stdClass with both post and author columns
    echo $row->a_name; // author name from the joined table
}

The join returns a Phalcon\Mvc\Model\Resultset\Simple that holds flat rows. If you need full model objects you can hydrate them with BlogPost::cloneResultMap(), but for a read‑only listing the flat resultset uses far less memory.

Caching metadata and resultsets

Phalcon stores model metadata (column maps, attribute types, relation definitions) in a metadata adapter. The default Memory adapter lives only for the request; for production you typically switch to Apcu or Stream so the metadata is reused across requests.

// config/services.php
$di->setShared('modelsMetadata', function () {
    return new \Phalcon\Mvc\Model\MetaData\Apcu([
        'prefix' => 'myapp_meta_',
        'lifetime' => 86400
    ]);
});

Query results can be cached through the modelsCache service. Wrapping the PHQL execution with a cache key avoids the database hit for hot pages.

$cacheKey = 'latest_posts_with_authors';
$results = $this->modelsCache->get($cacheKey);

if ($results === null) {
    $phql = "SELECT p.*, a.* FROM BlogPost p JOIN Author a ON p.author_id = a.id ORDER BY p.created_at DESC LIMIT 50";
    $results = $this->modelsManager->executeQuery($phql);
    $this->modelsCache->save($cacheKey, $results, 300); // 5‑minute TTL
}

foreach ($results as $row) {
    echo $row->a_name;
}

Note that the cached object is the resultset itself, not the raw SQL. On cache hit Phalcon re‑hydrates the rows without touching the database.

Operational trade‑offs

• Extension installation: Phalcon is a C extension (built via Zephir). You install it with pecl install phalcon or your distro's package manager, then add extension=phalcon.so to php.ini. This differs from pure‑PHP frameworks that only need Composer.
• Debugging internals: Because framework code lives in the extension, you cannot step into it with Xdebug the way you can with vendor/ code. You rely on Phalcon's own logging (e.g., Phalcon\Db\Adapter\Pdo\Mysql::setLogger()) to inspect generated SQL.
• Upgrades: A new Phalcon version requires rebuilding or updating the extension and restarting PHP‑FPM/Apache. In a pure‑PHP stack you would just run composer update.

Verify the fix locally

1. Install Phalcon 5.x on PHP 8.x (run as root or with sudo):

   pecl install phalcon
   # add the line below to your php.ini (path varies by distro)
   echo "extension=phalcon.so" >> /etc/php/8.3/cli/php.ini
   

2. Confirm the extension loads:

   php -m | grep phalcon
   # expected output: phalcon
   

3. Enable query logging in a bootstrap file:

   $di->setShared('db', function () {
       $adapter = new \Phalcon\Db\Adapter\Pdo\Mysql([
           'host' => '127.0.0.1',
           'username' => 'app',
           'password' => 'secret',
           'dbname' => 'blog',
           'options' => [
               PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
           ]
       ]);
       $logger = new \Phalcon\Logger\Adapter\Stream('php://stdout');
       $adapter->setLogger($logger);
       return $adapter;
   });
   

4. Run the lazy version and count the SELECT lines in the log (expect ~51).
5. Run the PHQL version (with or without cache) and verify only a single SELECT appears.

If the query count drops from N+1 to 1, the refactor works. You can also measure memory with memory_get_peak_usage() before and after to see the resultset‑vs‑model difference.

Next steps

Apply the same pattern to any endpoint that iterates a relation in a loop. Start by enabling the metadata adapter (APCu or Stream) in production, then add a modelsCache service backed by Redis or Memcached for resultset caching. Monitor the query log after each change; the goal is a predictable, low query count regardless of page size.