Does Hibernate Criteria supports query: select ...from (select ...)

Hibernate ORM
1

SELECT result.id, s1_0.field1,
s1_0.field2,
s1_0.field3,
s1_0.field4
FROM (
SELECT i1_0.id, i1_0.foreign_fk
FROM table1 i1_0
) result
LEFT JOIN table2 s1_0 ON s1_0.id = result.foreign_fk

I am using Hibernate 6.6.13.Final. When I try to convert this query into Hibernate JPA Criteria API, it does not work.

Does the JPA Criteria API support this type of query (subquery in the FROM clause and join)?

2

Hibernate ORM JPA Criteria extensions do support this, but unfortunately, the documentation for this part was not yet written: Hibernate ORM User Guide

You will need to define the subquery and then pass it to from:

final JpaCriteriaQuery<Tuple> cteSubquery  = cb.createTupleQuery();
JpaSubQuery<Tuple> data1 = cteSubquery.subquery(Tuple.class);
final JpaRoot<Invoice> root = cteSubquery .from(Invoice.class);
data1.multiselect(
	root.get(Invoice_.id).alias("id"),
	root.get(Invoice_.isPToPay).alias("isPToPay"),
	root.get(Invoice_.invoiceNumber).alias("invoiceNumber"),
	root.get(Invoice_.supplier).get(Supplier_.id).alias("supplierFk"));
		
JpaRoot<Tuple> subqueryRoot = cteSubquery.from(data1);
JpaEntityJoin<Supplier> supplier = subqueryRoot.join(Supplier.class, JoinType.LEFT);
supplier.on(supplier.get(Supplier_.id).equalsTo(subqueryRoot.get("supplierFk")));
3 
it does not work for this line 
**JpaEntityJoin<Supplier> supplier = subqueryRoot.join(Supplier.class, JoinType.LEFT);**
Since Tuple from a subquery doesn’t have mapped relationships, you can’t directly join it to Supplier.