less doctrine queries in symfony admin modules

Scene from "The Damned" by Luchino Visconti (1969)

basic stuff

This short article is a hint for symfony newbies. I'll show how to configure admin modules to limit the number of SQL queries called to generate the list of result.

the problem

The problem occurs when we create an admin module for a model that has some related records which we want to show. By default, Doctrine is not smart enough to guess that joining those related records will generate less SQL queries, so - depending on the database structure - generating a list of 20 objects can generate hundreds of queries (or even thousands, if the there are so many related records to be displayed). The example below is not so tragic, there are only 20 useless queries.



By default, symfony calls a basic

SELECT * FROM model;

query first. Then, while generating the row for each record, it calls for all related objects separately. As we can see below, these are the same queries run for different ids. A great waste of resources.



Now, the thing is to make symfony call less SQL queries to do the same job.

the solution

Let's say, we have a custom model called Proc, representing a procedure.



Go to the generator.yml file in the custom admin module config directory and add the table_method option in the list section:

generator:
  class: sfDoctrineGenerator
  param:
    #...
    config:
      list:
        table_method: getListQuery

This means that the configuration tells the admin module controller that if it want to fetch objects to display in a list, it has to look for the getListQuery in the ProcTable class. So we shall define it:

public static function getListQuery()
{
  return Doctrine_Query::create()
    ->from('Proc p')
    ->leftJoin('p.Tags');
}

And it's all. Fast, easy and clean.

There's also a possibility to achieve the same goal by overriding the buildQuery method in the actions class of the custom admin module (apps/backend/modules/proc/actions.class.php), but it's the dirty solution (thus it's not advisable):

protected function buildQuery()
{
  $query = parent::buildQuery();
  return $query
    ->leftJoin($query->getRootAlias().'.Tags');
}

The result

Now, each Proc object has all related Tag objects fetched already. In the previous situation, when symfony was generating a row for an object (in the admin module list) it called the object for its related objects. None were fetched, so SQL queries were called. Now, when the Proc object is called for its Tags, they're already there, so no additional SQL queries are called.



This limits number of queries and is very important (ignoring this may cause the server to go down frequently). Each action (not only admin module) should be optimised to make symfony call as little SQL queries as possible. Of course, you may join all related models, not only one (the Proc model could have more than only Tags relation, for example, Category, Author, Department and so on - each of them would generate useless queries as well).



Enjoy ;)

EDIT: This post was updated thanks to fizyk's and sacri's opinions. Thanks, guys ;).