Geek asks and answers

How to sort a Doctrine DQL query by number or members of a relationship?

I am trying to create a query to retrieve objects from a Doctrine database, sorted by the number of members of a specific one-to-many relationship.

In particular: I have two entities: a person and a federation. A person can be a member of one federation (a person has a "federation" relationship), and a federation can have Russian people (a federation as a "people" relationship).

I would like to create a DQL query that returns a list of federations ordered by how many people are members of this Federation. Something like that:

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

This will be the first step. There is an additional second step, which I do not know whether it is possible to achieve with a single query, which will filter the members of the relationship before counting. For instance:

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

This second would be the optimal result, but the first satisfies my needs if the second case is not possible in one request.

Thanks in advance.

+5
source share
2 answers

In total there are 5 DQL functions (Doctrine 2.2): AVG, COUNT, MIN, MAXand 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 cheating, I suggest you look at the Doctrine white papers .

+4
source

You can do something line by line:

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, which means that the selected field will not be in the results, in which case it means that you simply return entities instead of an array.

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

+6

More articles:


All Articles