QueryDSL和SubQuery的元组条件查询

Question

我正在尝试使用QueryDSL编写一个查询，以按其parentId分组获取表中最古老的元素。

对应的SQL语句应该是:

SELECT a.* FROM child a
    INNER JOIN
    (
        SELECT parentId, MAX(revision) FROM child GROUP BY parentId
    ) b
    ON ( 
        a.parentId = b.parentId AND a.revision = b.revision
    )

现在在QueryDSL中，我被语法卡住了。

JPQLQuery<Tuple> subquery = JPAExpressions
                .select(child.parent, child.revision.max())
                .from(child)
                .groupBy(child.parent);

HibernateQuery<Child> query = new HibernateQuery<>(session);
query.from(child)
    .where(child.parent.eq(subquery.???).and(child.revision.eq(subquery.???))));

如何使用子查询编写此查询？

表看起来像这样：

___parent___（在此查询中未使用，但存在）
parentId
P1 | *
P2 | *
P3 | *
___child___
parentId | revision
P1 | 1 | *
P1 | 2 | *
P1 | 3 | *
P2 | 2 | *
P2 | 3 | *
P3 | 1 | *
___child 的结果，每个 parentId 的最高修订版___
P1 | 3 | *
P2 | 3 | *
P3 | 1 | *

我尝试过的：

.where(JPAExpressions.select(child.parent,child.revision).eq(subquery));

-> org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected end of subtree

还有许多语法错误...

目前我使用了一个不太好的循环，因为我还没有找到解决办法。

- Guillaume F.

---

子表中是否有唯一键？ - Timo Westkämper

是的，child.id和parent.parentId分别是主键。(非常感谢您抽出时间回答 :) ) - Guillaume F.

Answer 1

您可以使用Expressions.list()来指定多列作为in子句的条件：

query.from(child).where(Expressions.list(child.parent, child.revision).in(subquery));

另一种选择是使用innerJoin()，就像你原来的SQL语句中一样。

- Timi

Answer 2

Expressions.list(ENTITY.year, ENTITY.week).in(//
                    Expressions.list(Expressions.constant(1029), Expressions.constant(1)),
                    Expressions.list(Expressions.constant(1030), Expressions.constant(1)),
                    Expressions.list(Expressions.constant(1031), Expressions.constant(1))

你可能在寻找的是 QueryDSL，但它会生成错误的 SQL：

((p0_.year , p0_.week) in (1029 , 1 , (1030 , 1) , (1031 , 1)))

- JRA_TLL

---

你的解决方案在mysql中给了我一个错误，如Operand should contain 2 column(s)。 - Diego Macario

Answer 3

在JPA中，子查询只能出现在where部分。

以下是我对您的查询的看法。

select(child).from(child).where(child.revision.eq(
  select(child2.revision.max())
 .from(child2)
 .where(child2.parent.eq(child.parent))
 .groupBy(child2.parent))).fetch()

- Timo Westkämper

---

感谢您的回答。当修订号相同时，此查询无法工作。Child.revision 不是唯一的。我的查询中没有 "child2"，只有一个父级和它的子级。这就是为什么我需要在查询的 Join On 部分匹配元组，即 parentId 和 revision 的两个值。 - Guillaume F.

孩子版本在父级范围内是唯一的吗？如果不是，那么这将确实为每个父级返回多行。 - Timo Westkämper

我在问题中添加了数据示例。 - Guillaume F.

嗨@TimoWestkämper，我有一个类似的查询，但是如果我在那里放置eq()，运行查询时会出现此错误：“无法提取ResultSet; SQL [n / a];嵌套异常是org.hibernate.exception.DataException：无法提取ResultSet”。我认为子查询以某种方式返回了一个结果集而不是单个结果。当我使用in（）时，查询可以运行，但在某些边角情况下结果并非如预期。 - noname

Answer 4

基于 JRA_TLL 的回答 - 嵌套使用 Expressions.list()，根据 QueryDSL 的维护者们的说法，不受支持。以下是他们的一句话：

这并不是一个真正的 bug，而只是对 QueryDSL 内部元组 list 表达式的不正确使用。

[...]

解决方案很简单：不要使用 list 表达式。而是使用 Template 表达式。

这里是 JRA_TLL 的回答版本，使用了维护者推荐的 Template 范例，可以做到正确：

public static SimpleExpression<Object> tuple(Expression<?>... args) {
    return Expressions.template(Object.class, "({0}, {1})", args);
}

// ...

Expressions.list(ENTITY.year, ENTITY.week).in(
    tuple(Expressions.constant(1029), Expressions.constant(1)),
    tuple(Expressions.constant(1030), Expressions.constant(1)),
    tuple(Expressions.constant(1031), Expressions.constant(1)));

这应该会生成正确的SQL语句：

(p0_.year , p0_.week) in ((1029 , 1) , (1030 , 1) , (1031 , 1))

请注意，这种查询构建方式并不适用于所有类型的数据库；例如，这在PostgreSQL中是有效的，但在Microsoft SQL Server中无效。

- kashev