Get objects and related count in one shot

Sometimes you just need to output the number of objects related to another, but this simple operation can be a major blow performance-wise. I hope this trick I use a lot in my symfony + doctrine developments will save you some time.

Let’s consider a blog that allows you to tag your posts:

BlogPost:
  columns:
    title: string(255)
    body: clob
  relations:
    Tags:
      class: Tag
      foreignAlias: BlogPosts
      refClass: BlogPostTag
      local: blog_post_id
      foreign: tag_id

Tag:
  columns:
    name: string(255)

BlogPostTag:
  columns:
    blog_post_id:
      type: integer
      primary: true
    tag_id:
      type: integer
      primary: true
  relations:
    BlogPost:
      local: blog_post_id
      foreign: id
      foreignAlias: BlogPostTags
    Tag:
      local: tag_id
      foreign: id
      foreignAlias: BlogPostTags

You can retrieve this schema in the symfony 1.x documentation

Now, we build an admin generator which shows the number of tags per blog post on the list, with 20 results per page. This means we will have 1 SQL request to retrieve the 20 posts and 1 SQL request per post to retrieve the tag count. Taking into account the count request of the pager, we will have a total of 22 requests. This will get worse if we choose to display more blog posts at a time.

There is a way to optimize this with Doctrine!

Add count into the query

Let’s add the calculation of the tag count to the request that retrieves the blog posts.

It could look like that:

  // lib/model/doctrine/BlogPostTable.class.php

  /**
   * Find a blog post by its id.
   * @param integer $id
   * @return BlogPost|false
   */
  public function findById($id)
  {
    // Subquery that counts the number of tags per post.
    $sub_query = '(SELECT COUNT(t.id) FROM BlogPostTag t WHERE blog_post_id = '.$id.')';

    $query = $this->createQuery('bp')
      ->select('bp.*')
      ->addSelect($sub_query.' as nb_tags') // the number of tags will be in the nb_tags variable
      ->where('bp.id = ?', $id);

    return $query->execute();
  }

Explanations

• The $subquery counts the number of tags for the blog post in SQL
• Create a query that retrieves blog post by its id
• Add the $subquery into the select with an alias ‘nb_tags’. You have to specify what you want to select first to use the addSelect method, otherwise, it will not work.
• Return the execution of the query

Result

The result of the query should be an instance of a Doctrine_Record (false if no blog post is found) which contains the result of the subquery into its protected array $_values. As it is a protected attribute of the Doctrine_Record class it can be accessed in your BlogPost model class.

Create a smart getter

So now that we get the value of ‘nb_tags’ into the hydrated record we can write a getter that returns this value in a smart way.

First of all, you should add an attribute to your model class to store the number of tags:

  // lib/model/doctrine/BlogPost.class.php

  /**
   * The number of tags of the blog post.
   * @var Integer
   */
  protected $nb_tags = null;

Then, implement the getNbTags() that will return the value of the ‘nb_tags’ key in the $_values array of the doctrine record. But what if the record has been found by using another query? The ‘nb_tags’ will not exist so you have to test it otherwise you might face an exception. This is how you should write your getter:

  // lib/model/doctrine/BlogPost.class.php

  /**
   * Return the number of tags related to the blog post.
   *
   * @return Integer
   */
  public function getNbTags()
  {
    // The number of tags is not yet set
    if (is_null($this->nb_tags)
    {
      // the variable added in the SQL request will be found in the $_values of the doctrine record
      if (isset($this->_values['nb_tags']))
      {
        $this->nb_tags = $this->_values['nb_tags'];
      }
      else
      {
        /**
         * The number of tags has not been set in the SQL request
         * Doctrine will lazy load every tag and count them all.
         * This could be optimized by overwriting the createQuery method,
         * adding a left join to the tag table automatically in BlogPostTable.class.php
         * (beware, it can lead to unwanted side effects)
         */
        $this->nb_tags = $this->getTags()->count();
      }
    }

    return $this->nb_tags;
  }

Conclusion

So what have we achieved? Simple: we reduced the number of SQL requests in our admin gen from 22 to 2! One to retrieve the blog posts with the number of related tags, and the other by the Doctrine pager. Obviously, this trick isn’t restricted to admin generators, so think of the many situations where you can use it!