我可以将此SQL代码重写为querydsl代码吗

Question

我有一个sql代码，它计算数据库中不同谓词的记录数

SELECT
count(*) as total_count,
count(notAided) as not_aided_count,
count(chatAided) as chat_aided_count
FROM (
SELECT
CASE WHEN aided_with IS NULL THEN 1 END notAided,
CASE WHEN aided_with = 'CHAT' THEN 1 END chatAided
FROM sessions
) sessions

我可以用querydsl重写它吗？我看了一下CaseBuilder，但不知道如何在选择查询中使用它

Answer 1

可以写：

query.select(
QSession.session.id.count().as("total_count"),
new CaseBuilder().when(QSession.session.aidedWith.isNotNull()).then(1).otherwise(Expressions.<Integer> nullExpression()).count().as("not_aided_count"),
new CaseBuilder().when(QSession.session.aidedWith.eq("CHAT")).then(1).otherwise(Expressions.<Integer> nullExpression()).count().as("chat_aided_count")
)

这相当于SQL:

SELECT
count(*) as total_count,
count(CASE WHEN aided_with IS NULL THEN 1 END) as not_aided_count,
count(CASE WHEN aided_with = 'CHAT' THEN 1 END) as chat_aided_count
FROM sessions