ORDER BY statement for Symfony2 Doctrine OneToMany/ManyToMany relationships

April 23rd 2013

The simple answer

You just need to use the @ORMOrderBy() statement in your entity when you declare the relationship:


/**
 * @ORMOneToMany(targetEntity="images", mappedBy="gallery")
 * @ORMOrderBy({"entity_attribute" = "DESC", "another_entity_attribute" = "ASC"})
 */

protected $images;

The longer answer with Symfony2 application

I have been building a multi-site micro CMS using Symfony2. To order the pages I have created a Hierarchical tree traversal system. Because there are multiple sites in the database, each page has its own id field too as the left and right values are not unique. On the administration side, there was no issue. In 2011 I wrote an admin system for a besboke eCommerce site in Symfony 1.4 and created a full tree traversal system with jQuery drag and drop ordering. So a few weeks ago I ported my old code into Symfony2 which was remarkably easy as there are many similarities between the two frameworks.

The issue came on the front end. I am using the Symfony2 menu bundle from KnpLabswhich works a treat but in order to generate the side navigation I need to get the current active page and all its children. I added a OneToMany relationship on the pages entity to itself to get the children pages with one query:e into Symfony2 which only took a day ish.


/**
 * @ORMOneToMany(targetEntity="Page", mappedBy="parent")
 */

protected $children;

Unfortunatley because my page entity also has an id field, when I call $page->getChildren() the returned objects are in id order. Some quick google searching for "Symfony2 order OneToMany" returned a useful post from stackoverflow. One of the answers mentions the @ORMOrderBy statement which worked awesomely:


/**
 * @ORMOneToMany(targetEntity="Page", mappedBy="parent")
 * @ORMOrderBy({"lft" = "ASC"})
 */

protected $children;

Happy days, once again Symfony2 and Doctrine have an awesome solution for me!

Luke Rotherfield

Symfony2 Developer in CT USA. Luke is a Symfony2 wizard and has written some sweet libraries of his own. Luke loves Jesus, his gorgeous wife and his two beautiful daughters :)