QueryDSL and SubQuery with Tuple condition

Question

I am trying to write a query in QueryDSL to fetch the oldest elements of a table grouped by their parentId.

The SQL equivalent should be:

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
    )

Now in QueryDSL I'm stuck with the syntax.

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.???))));

How do you write this query using a subquery ?

The tables are looking like this :

___parent___ (not used in this query, but exists)
parentId
P1       | *
P2       | *
P3       | *

___child___
parentId | revision
P1       | 1       | *
P1       | 2       | *
P1       | 3       | *
P2       | 2       | *
P2       | 3       | *
P3       | 1       | *

___result from child, the highest revision for each parentId___
P1       | 3       | *
P2       | 3       | *
P3       | 1       | *

What I've tried so far :

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

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

and many syntax errors ...

I use a dirty loop, for now, since I haven't found a solution yet.

Yes, child.id and parent.parentId are the respective primary keys. (thanks a lot for taking the time to answer :) ) — Guillaume F., Dec 02 '15 at 18:36

score 6 · Accepted Answer · answered Jun 10 '17 at 15:28

6

You can use Expressions.list() to specify more than one column for the in clause:

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

The alternative is to use innerJoin(), as in your original SQL.

answered Jun 10 '17 at 15:28

Timi

774
9
16

score 3 · Answer 2 · answered Nov 18 '20 at 07:00

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))

would be what you are looking for, but QueryDSL generates wrong SQL from it:

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

Your solution gave me a error in mysql, as `Operand should contain 2 column(s)` — Diego Macario, Jun 21 '21 at 00:26

score 1 · Answer 3 · answered Dec 02 '15 at 19:05

1

In JPA subqueries can appear only in the where part.

Here is my take on your query

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

answered Dec 02 '15 at 19:05

Timo Westkämper

21,824
5
78
111

Thanks for the answer. This query doesn't work when revision numbers are identical. Child.revision is not unique. There is no "child2" in my query, only a parent and its children. This is why I need to match the tuple, both values of parentId and revision in the Join On part of the query – Guillaume F. Dec 02 '15 at 19:11
Ok, is child revision unique in the scope of a parent? If not, then this will return indeed return multiple rows per parent. – Timo Westkämper Dec 21 '15 at 19:10
I added an example of the data in the question. – Guillaume F. Dec 22 '15 at 10:39
Hi @TimoWestkämper, I have a similar query, however if I put eq() there, it gives this error when running the query: "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet". I think somehow the subquery is returning a resultset instead of a single result. And when I use in() the query can run, but the result is not as expected in some corner case. – noname Jan 21 '21 at 11:05

kashev · Answer 4 · 2023-02-16T20:50:00.120

Building off of JRA_TLL's answer - nested use of Expressions.list() is, per the maintainers of QueryDSL, not supported. Choice quote:

This is not really a bug, is just improper use of QueryDSL's internal list expressions for tuples.

[...]

The solution is quite simply: don't use the list expression for this. This use a Template expression.

Here's a version of JRA_TLL's answer with the Template paradigm recommended by the maintainers, which does the right thing:

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)));

This should generate the right SQL:

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

Note that this kind of query construction doesn't work for all kinds of databases; for instance, this is valid in PostgresSQL, but isn't valid in Microsoft SQL Server.

QueryDSL and SubQuery with Tuple condition

4 Answers4

Linked