JPA: join subquery

[wangyedong66]

In JPA:
sql:
select A.* from A join (select aid from B group by aid) b on b.aid=A.id;
How to do?

[jwgmeligmeyling]

This is not possible in JPQL, because subqueries are only allowed in the SELECT, WHERE and HAVING clauses. As a result, QueryDSL doesn't support them in JPA queries. QueryDSL does support subquery joins in SQL queries (but that's of no use for JPA).

Blaze-Persistence is an extension for JPA. Their flavour of JPQL, JPQL,Next, does support joining subqueries through common table expressions. There is a QueryDSL integration described here, Using that extension, you could write;

QRecursiveEntity recursiveEntity = new QRecursiveEntity("t");

List<RecursiveEntity> fetch = new BlazeJPAQuery<>(entityManager, cbf)
    .select(someEntity, recursiveEntity)
    .from(someEntity)
    .join(select(recursiveEntity)
        .from(recursiveEntity)
        .where(recursiveEntity.parent.name.eq("root1"))
        .orderBy(recursiveEntity.name.asc())
        .limit(1L), recursiveEntity)
    .on(recursiveEntity.something.eq(someEntity))
    .fetch();

This API is analog to subquery joins in querydsl-sql.

The Blaze-Persistence QueryDSL integration even supports lateral joins (joins that reference outer query variables, also known as CROSS APPLY):

QRecursiveEntity t = new QRecursiveEntity("t");
QRecursiveEntity subT = new QRecursiveEntity("subT");
QRecursiveEntity subT2 = new QRecursiveEntity("subT2");

List<Tuple> fetch = new BlazeJPAQuery<>(entityManager, cbf)
    .select(t, subT2)
    .from(t)
    .leftJoin(select(subT).from(t.children, subT).orderBy(subT.id.asc()).limit(1), subT2)
    .lateral()
    .fetch();

Disclaimer: I am a QueryDSL and Blaze-Persistence contributor.

[GauravRatnawat]

QueryDsl does not support subQuery in join but you can achieve this via following way:

Map a View or SQL query to JPA entity:

import lombok.Setter;
import org.hibernate.annotations.Subselect;
import org.hibernate.annotations.Synchronize;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
@Getter
@Setter
@Subselect("select aid from B group by aid")
@Synchronize("B")
public class BGroupByAid {

    @Id
    private Integer aId;
}

then use the equivalent QueryDSl entity in the class just like the regular entity:

JPAQuery<QAsset> query = new JPAQuery<>(entityManager);  
QBGroupByAid bGroupById = QBGroupByAid.bGroupByAid;

 List<A> tupleOfAssets =
            query.select(A)
        .from(A).innerJoin(bGroupById).on(bGroupById.aId.eq(A.aId))
        .fetchResults()
        .getResults();