Order Doctrine manyToOne collection differently in different controllers

Question

Using Synfony2 with Doctrine, I have an Entity (say Country) with a ManyToOne (say City) relation to another. I use the @ORM\OrderBy annotation to tell Doctrine how to fetch the association by default.

I want to display the cities ordered by name in a page, and by size in another, so I want the orderBy being different in the two controllers. Is there a way to tell Doctrine the orderby rule the proxy object should use when hydrating at run time ?

My current working solution is to have two different methods in the repository to fetch the City and set the ORDER BY in DQL. The drawback of this solution is that I have to give Country and City array as two parameters in my twig template, and if the designer use country.cities instead of the cities parameter it will fail:

$country = ...
$cities = $this->getDoctrine()->getRepository("MyBundle:City")->findFromCountryOrderedBySize($country);
return $this->render('MyBundle:Section1:index.html.twig', array(
    'country'=>$country,
    'cities'=>$cities
));

You could just $country->setCities($cities); in the controller or service then tell your designer to always use $country->getCities(). It also might make it more flexible for the future should your order requirements change. — AlexP
It may also work better to use a QueryBuilder instead of a repository to have more granular control over the result set. apigen.juzna.cz/doc/doctrine/doctrine2.git/… — Rottingham
@AlexP it's so simple that I did not even though at it ;) It's a good idea. — Florent

Rottingham Rottingham · Accepted Answer · 2013-12-27T22:24:20

When I need finer control over the result set I will use a QueryBuilder. The repo's are nice for a quick findOneBy() call, but not when you have a larger WHERE clause and multiple orderBy definitions.

$qb = $this->getDoctrine()->createQueryBuilder();
$query = $qb->select('c')
   ->from('City', 'c')
   ->orderBy('c.size')
   ->getQuery();

$cities = $query->getResult();

The documentation is here: http://apigen.juzna.cz/doc/doctrine/doctrine2.git/class-Doctrine.ORM.QueryBuilder.html

Order Doctrine manyToOne collection differently in different controllers

2 Answers