Prendre un Café

I’m currently working on a big Symfony project, with a lot of Doctrine models and complex queries to write. I found a way to organize all of them in an object-oriented and cleaner way than using the traditionnal addNamedQuery() and createNamedQuery() methods workflow^[1].

The idea is to create dedicated query classes for a given model ; this way, you can provide useful methods to build the business-related parts of your query.

As usual, the theory is more understandable with a concrete example. Let’s consider this simple Doctrine model^[2] :

Disclaimer: The provided examples have been written in a hurry, so mistakes might have been not detected by my attentive proof-reading

 yaml
BlogAuthor:
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true  
    name:
      type: string(255)
  relations:
    Post:
      type: one
      class: BlogPost
      local: id
      foreign: author_id

BlogPost:
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    author_id:
      type: integer(4)
      notnull: true
    title:
      type: string(255)
    content:
      type: string(65535)
  relations:
    Author:
      type: one
      class: BlogAuthor
      local: author_id
      foreign: id
    Comments:
      type: many
      class: BlogComment
      local: id
      foreign: post_id

BlogComment:
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    post_id:
      type: integer(4)
      notnull: true
    author:
      type: string(255)
    content:
      type: string(5000)
  relations:
    Post:
      type: one
      class: BlogPost
      local: post_id
      foreign: id

Now let’s imagine a Query class dedicated to query the BlogPost table:

 php
<?php 
class BlogPostQuery extends Doctrine_Query
{
  static public function create($conn = null, $class = null)
  {
    return parent::create($conn, 'BlogPostQuery')
      ->from('BlogPost p');
  }
  
  public function addPosts($fields = 'p.*')
  {
    return $this->addSelect('p.*');
  }
  
  public function addComments($fields = 'c.*')
  {
    return $this
      ->addSelect($fields)
      ->leftJoin('p.Comments c')
      ->addGroupBy('c.id');
  }
  
  public function addAuthors($fields = 'a.*')
  {
    return $this
      ->addSelect($fields)
      ->leftJoin('p.Author a')
      ->addGroupBy('a.id');
  }
  
  public function addCommentsCount($alias = 'nb_comments')
  {
    return $this
      ->addSelect(sprintf('COUNT(c.id) as %s', $alias))
      ->addGroupBy('c.id');
  }
  
  public function filterByAuthorName($authorName)
  {
    return $this
      ->andWhere('a.name = ?', $authorName);
  }
}

So how can we use this query object? Here are some sample uses:

 php
// Retrieve all posts
$posts = BlogPostQuery::create()
  ->addPosts()
  ->fetchArray();

// Retrieve all posts with comments
$posts = BlogPostQuery::create()
  ->addPosts()
  ->addComments()
  ->fetchArray();

// Retrieve all posts with comments and their count per post
$posts = BlogPostQuery::create()
  ->addPosts()
  ->addComments()
  ->addCommentsCount('yataa')
  ->fetchArray();

// Retrieve all post with chuck as its author and related comments
$posts = BlogPostQuery::create()
  ->addAuthors()
  ->addPosts()
  ->addComments()
  ->filterByAuthorName('chuck')
  ->fetchArray();

// and so on...

Of course, this example of use is not really relevant as our model is really simple, but when you’re dealing with dozens of internationalized objects, it can help cleaning your model classes, controllers and improving the organization of your work.

Update and important precisions

Some people are having negative feedback regarding this technique, claiming it will encourage people using the custom query object directly in the controllers; that’s absolutely not the case as the queries are to be used only within the model layer, for example in the BlogPostTable class:

 php
<?php
class BlogPostTable extends Doctrine_Table
{
  static public function getPostsWithCommentsByAuthor($authorName)
  {
    return BlogPostQuery::create()
      ->addPosts()
      ->addComments()
      ->filterByAuthorName($authorName)
      ->fetchArray()
    ;
  }
}

And in a controller:

 php
class blogActions extends sfActions
{
  public function executeListByAuthor(sfWebRequest $request)
  {
    $this->posts = BlogPostTable::getPostsWithCommentsByAuthor($request->getParameter('author'));
  }
}

During a trip back to Paris by train, I quickly coded for a project of mine a new brush for SyntaxHighlighter, a client-side syntax coloring library powered by Javascript, in order to highlight YAML code snippets.

You can get the code here and provide feedback by sending me an email.

Edit: Oops, didn’t find this already existing brush (I hadn’t wifi access in the train). I think my version support more specific aspects of the syntax, though.

Mon Dieu, les billets techniques refleuriraient-ils au printemps ? Si vous désirez récupérer des informations géographiques à partir de l’adresse IP (ou du hostname) d’un utilisateur, vous pouvez utiliser les fonctions fournies par l’extension PECL GeoIP.

Voici la procédure d’installation sur une Ubuntu 8.04:

$ sudo -s
# apt-get install build-essential php5-dev php5-cli libgeoip-dev libgeoip1 php-pear
# pecl install geoip

Si toiut s’est bien passé :

# echo "extension=geoip.so" >> /etc/php5/cli/php.ini

Si vous utilisez Apache comme serveur :

# echo "extension=geoip.so" >> /etc/php5/apache2/php.ini

Il faut également installer la base GeoIPCity de Maxmind :

# wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz
# gunzip GeoLiteCity.dat.gz
# mv GeoLiteCity.dat /usr/share/GeoIP/GeoIPCity.dat

Vous pouvez maintenant tester l’extension avec une ligne du genre^[1] :

$ echo "<?php var_dump(geoip_record_by_name('209.202.168.**'));"|php

Ça donne ici :

array(11) {  ["continent_code"]=>
  string(2) "NA"
  ["country_code"]=>
  string(2) "US"
  ["country_code3"]=>
  string(3) "USA"
  ["country_name"]=>
  string(13) "United States"
  ["region"]=>
  string(2) "NC"
  ["city"]=>
  string(4) "Cary"
  ["postal_code"]=>
  string(5) "27511"
  ["latitude"]=>
  float(35.7********)
  ["longitude"]=>
  float(-78.7*******)
  ["dma_code"]=>
  int(560)
  ["area_code"]=>
  int(919)
}

Enjoy.

Sorry for the lame title of this blog post, but I’m happy enough to announce the immediate availability of both symfony 1.2 and Jobeet, the new symfony advent calendar!

Jobeet It is a set of 24 tutorials, published day-by-day between December 1st and Christmas. Each tutorial is meant to last one hour, and will be the occasion to see the ongoing development of a web application with symfony, from A to Z.

So don’t hesitate to start reading day one of this great tutorial

Edit: I forgot to mention the simultaneous release of the symfony and Doctrine book