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 ;).

documenting database with symfony doctrine

Scene from "Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb" by Stanley Kubrick (1964)

Symfony projects using Doctrine ORM have their schema defined in schema.yml files. There's a very nice & useful feature, setting comments to your table columns (which is probably not mentioned in the official docs).

It's very easy - just set a comment option to each of your columns, defining what this column holds, for example:

Customer:
  actAs:
    Timestampable: ~
    Signable: ~
  columns:
    firstname:
      type: string(32)
      notnull: true
      comment: "imię"
    lastname:
      type: string(64)
      notnull: true
      comment: "nazwisko"
    email:
      type: string(64)
      notnull: true
      comment: "adres E-mail"

The MySQL code generated for such schema is already with provided comments:

CREATE TABLE customer (
  id BIGINT AUTO_INCREMENT,
  firstname VARCHAR(32) NOT NULL COMMENT 'imię',
  lastname VARCHAR(64) NOT NULL COMMENT 'nazwisko',
  email VARCHAR(64) NOT NULL COMMENT 'adres E-mail',
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  created_by BIGINT DEFAULT '1' NOT NULL,
  updated_by BIGINT DEFAULT '1' NOT NULL,
  INDEX created_by_idx (created_by),
  INDEX updated_by_idx (updated_by),
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = INNODB;

This is how phpmyadmin makes use of column comments:



And below, you can see another example of comments usage - an important database table that many other tables rely on (click to enlarge):



Short and precise description makes a table structure easier to be understood by a programmer.

It takes very little time to define those database comments. But it can save you a lot of time: either if you are working on a big project that has to be well-documented and need to review the structure or if the project is going to be extended by someone else who doesn't know what is where, or you may find it useful in many different situations.