Doctrine QueryBuilder HAVING表达式

Question

OK, so I'm rewriting some code, using Doctrine ORM (2.5).

The old code creates a query that has something like this:

SELECT * FROM couples INNER JOIN individuals ON (couples.id = individuals.couple_id)
GROUP BY couples.id
HAVING SUM(individuals.date_of_birth <= '1976-01-01') > 0

I have no clue how to implement this best using the Doctrine QueryBuilder. This is a very simplified example, the real query is much longer and has a few HAVING clauses, all of which use SUM(some_condition) > 0 to ensure that only Couples that contain a matching Individual will be retrieved.

I can add having clauses in Doctrine using $queryBuilder->having(), but I cannot do so using the SUM() function. Any ideas?

Answer 1

Actually, there is nothing stopping you from using sum together with having:

$queryBuilder
    ->select('couple, individual')
    ...
    ->having('sum(individual.date_of_birth) > 0');

The sum() function of the query builder, actually takes in two arguments and returns a mathematical expression which is not what you're after.

When you use sum on a field like in the example above, that's actually the aggregating function.

Another thing to keep in mind in your case is that, according to the documentation, every having() call overrides all previously set having conditions. So if you want to use multiple of these, use andHaving and orHaving.

I hope this explains it.