Doctrine QueryBuilder HAVING表达式(Doctrine QueryBuilder HAVING expression)

Doctrine QueryBuilder HAVING表达式(Doctrine QueryBuilder HAVING expression)

好的，所以我正在使用Doctrine ORM（2.5）重写一些代码。

旧代码创建一个具有以下内容的查询：

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

我不知道如何使用Doctrine QueryBuilder实现这一点。 这是一个非常简化的示例，真正的查询更长，并且有一些HAVING子句，所有这些子句都使用SUM(some_condition) > 0来确保只检索包含匹配个体的Couples。

我可以使用$queryBuilder->having()在Doctrine中添加子句，但是我不能使用SUM()函数。 有任何想法吗？

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?

最满意答案

实际上，没有什么可以阻止你使用sum以及：

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

查询构建器的sum()函数实际上接受两个参数并返回一个不是你想要的数学表达式。

当您在上面示例中的字段上使用sum时，这实际上是聚合函数。

在你的情况下要记住的另一件事是，根据文档 ，每个having()调用都会覆盖之前设置的所有条件。 因此，如果您想使用其中的多个，请使用andHaving和orHaving 。

我希望这可以解释它。

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.