如何按照关系的数量或成员对Doctrine DQL查询进行排序？ [英] How to sort a Doctrine DQL Query by the number or members of a relation?

问题描述

我试图创建一个查询，用于从Doctrine数据库中检索对象，按特定一对多关系的成员数量排序。

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.

更具体地说：我有两个实体：人和联邦。一个人可以是一个联盟的成员（人有联盟的关系），联盟可能有 n 个人（联合为人关系）。

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).

我想创建一个DQL查询，返回联盟列表，按照多少人是该联盟的成员排序。沿着这些线：

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.

提前感谢。

推荐答案

您可以使用DQL中有5个聚合函数（Doctrine 2.2）： AVG ， COUNT code> MIN ， MAX 和 SUM 。

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

对于更多的DQL欺骗我建议你查找官方Doctrine文档。

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

这篇关于如何按照关系的数量或成员对Doctrine DQL查询进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！