问题描述
我试图创建一个查询，用于从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欺骗我建议你查找。
For more DQL trickery I suggest you look up official Doctrine docs.
这篇关于如何按照关系的数量或成员对Doctrine DQL查询进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！