QueryDSL and SubQuery with a Tuple Condition

Question

I am trying to write a query in QueryDSL to retrieve the oldest table items grouped by their parent id.

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 in 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 as follows:

  ___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 have 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 ...

At the moment I am using a dirty circuit, as I have not yet found a solution.

Answer 1

In subqueries, JPAs can only be displayed in that part.

Here is my request for your request

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

Answer 2

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

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