如何按关系的数量或成员对 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.

更具体地说:我有两个实体:个人和联邦.一个人可以是一个联盟的成员(Person 具有联盟"关系)，而一个联盟可能有 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、MIN、MAX 和 SUM.

There are 5 aggregate functions in DQL you can use (Doctrine 2.2): AVG, COUNT, MIN, MAX and SUM.

以下查询应该有效:

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屋！