如何计算QueryDSL中结果组的数量？ [英] How can I count the number of result groups in QueryDSL?
问题描述
如何在QueryDSL（Java中）实现组计数？
How can I implement a count-of-groups in QueryDSL (in Java)?
背景
我正在桌面上实施分页搜索，我想在返回之前对结果进行分组。
I'm implementing a paged search on a table, where I want to group the results before returning them.
除了通常情况 LIMIT x OFFSET y 查询，我也希望收到总行数。
In addition to the usual LIMIT x OFFSET y query, I also want to receive the total row count.
这是（简化）一页的SQL查询：
This is the (simplified) SQL query for one page:
SELECT x, y, MAX(z)
FROM tasks
WHERE y > 10
GROUP BY x, y
LIMIT 10 OFFSET 0
要检索行数，我试图使用天真的 COUNT（*）而不是 x，y，MAX（z）像这样：
To retrieve the number of rows, I tried to use a naive COUNT(*) instead of the x, y, MAX(z) like this:
SELECT COUNT(x)
FROM tasks
WHERE y > 10
GROUP BY x, y
不幸的是，这不产生一行上一个查询返回的组数，但每个组有一行，每行包含组合在一起的行数 - 通常用于聚合函数， COUNT 更改其含义当存在 GROUP BY 时。 （至少在Postgresql中，我们在这里使用的是什么。）
Unfortunately, this doesn't produce one row with the number of groups returned by the previous query, but one row for each group, each with the number of rows which were grouped together – as usual for aggregation functions, COUNT changes its meaning when a GROUP BY is present. (At least in Postgresql, what we are using here.)
我们可以使用第一个SELECT语句作为from子句中的子选择来检索总计数：
We can retrieve the total count by using the first SELECT statement as a subselect in a from clause:
SELECT COUNT(*)
FROM ( SELECT x, y, MAX(z)
FROM tasks
WHERE y > 10
GROUP BY x, y
)
现在困难的部分：我们如何在JPQL和/或QueryDSL中执行此操作？
Now the hard part: how can we do this in JPQL and/or QueryDSL?
前两个查询是由以下代码生成的：
The first two queries are were produced by code like this:
QTask qTask = QTask.task;
Expression<?>[] groupExps = { qTask.x, qTask.y };
Predicate predicate = qTask.y.gt(10);
List<Result> results = getQueryDSL().createQuery()
.from(qTask)
.where(predicate)
.groupBy(groupExps)
.offset(0)
.limit(10)
.list(ConstructorExpression.create(Result.class,
qTask.x
qTask.y,
qTask.z.max()))
Long total = getQuerydsl().createQuery()
.from(qTask)
.where(predicate)
.groupBy(groupExps)
.singleResult(qTask.x.count());
看起来像 JPA / JPQL不支持WHERE或HAVING子句以外的子选择，即它们在FROM子句中是不可能的。这似乎是JPA的QueryDSL也不支持它们的原因。
It looks like JPA/JPQL doesn't support subselects other than in WHERE or HAVING-clauses, i.e. they are not possible in the FROM clause. This seems to be the cause that QueryDSL for JPA doesn't support them either.
有没有办法重写这个语句，或以某种方式解决这个限制？
Is there a way to rewrite this statement, or somehow work around this limitation?
推荐答案
我无法帮助你解决jpa和jql问题，但这是一个有趣的SQL问题......
I can't help you out with jpa and jql, but it's an interesting SQL problem...
什么是RDMS？以下适用于MS SQL Server。您可以使用串联，distinct和count来确定连接x和y时有多少唯一值。当您按x，y分组时，这应该是相同的行数。
What RDMS? The below works in MS SQL Server. You can use concatenation, distinct and count to determine how many unique values there are when you concatenate x and y. This should be the same number of rows when you group by x,y.
select count(distinct CAST(x as char(10)) + CAST(y as char(10)))
from tasks;
这篇关于如何计算QueryDSL中结果组的数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
