How to sort a Doctrine DQL Query by the number or members of a relation?

Question

I'm trying to create a query for retrieving objects from a Doctrine database, sorted by the number of members of a specific one-to-many relationship.

More specifically: I have two Entities: Person and Federation. A person can be a member of one federation (Person has 'federation' relationship), and a federation may have n people (Federation as 'people' relationship).

I would like to create a DQL Query that would return the list of Federations, ordered by how many people are members of that Federation. Something along these lines:

SELECT f FROM AcmeStatsBundle:Federation f ORDER BY [number of members of f.people]

That would be the first step. There is an additional second step, which I don't know if is possible to achieve with a single query, which would be filtering the members of the relation prior the counting. Like so:

SELECT f FROM AcmeStatsBundle:Federation f ORDER BY [number of (f.people p where p.attr = value)]

That second one would be the optimal result, but the first one satisfies my needs, if the second case is not feasibly in a single query.

Thanks in advance.

Answer 1

You could do something along the lines of:

public function getFederationsOrderedByNumberOfPeople()
{
    return $this->createQueryBuilder('f')
        ->addSelect('COUNT(p) AS HIDDEN personCount');
        ->leftJoin('f.people', 'p');
        ->groupBy('f')
        ->orderBy('personCount', 'DESC');
}

The HIDDEN keyword was added in Doctrine 2.2, it means that the selected field won't be in the results, and in this case that means you just get your entities back instead of an array.

Reference to DQL SELECT documentation: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html#dql-select-examples

Answer 2

There are 5 aggregate functions in DQL you can use (Doctrine 2.2): AVG, COUNT, MIN, MAX and SUM.

The following query should work:

SELECT f
FROM AcmeStatsBundle:Federation f
LEFT JOIN f.people p
GROUP BY f.id
ORDER BY COUNT(p)
WHERE p.attr = :some_value

For more DQL trickery I suggest you look up official Doctrine docs.