Blaze Persistence & querydsl join subquery with CTE projections

Question

In Blaze Persistence with querydsl integration, it supports subquery in join statement. So I wonder how to combine projects with CTE entity in a join-subquery condition.

let's say I have two entities named person and pet. They are defined as this:

Person	Pet
id	id
name	personId
	age

Here is my test code:

blazeJPAQueryFactory.selectFrom(QPerson.person)
                .leftJoin(
                        JPQLNextExpressions
                                .select(Projections.bean(
                                                PersonPetCte.class,
                                                QPet.pet.personId.as(QPersonPetCte.personPetCte.personId),
                                                QPet.pet.age.sum().as(QPersonPetCte.personPetCte.ageSum)
                                ))
                                .from(QPet.pet)
                                .groupBy(QPet.pet.personId),
                        QPersonPetCte.personPetCte
                )
                .on(QPersonPetCte.personPetCte.personId.eq(QPerson.person.id))
                .where(QPersonPetCte.personPetCte.ageSum.gt(30))
                .fetch();

where PersonPetCte is declared as below (getters and stters omitted for brevity):

@CTE
@Entity
public class PersonPetCte {

    @Id
    Long personId;

    Long ageSum;
}

run this test results in the following exception: java.lang.UnsupportedOperationException: Select statement should be bound to any CTE attribute

Basically I want to achieve this: get all persons whose sum of their pet age is above 30.

I am trying to avoid string-hardcoded constant as much as possible, which is why I come across the idea of using CTE.

Please tell me if I am totally conceptually wrong or missing someting.

Answer 1

You almost got the syntax right, but Projections.bean does not provide enough metadata to deduce the mapping for the CTE.

Instead you have to do:

new BlazeJPAQuery<>()
   .from(QPet.pet)
   .groupBy(QPet.pet.personId)
   .bind(QPersonPetCte.personPetCte.personId, QPet.pet.personId)
   .bind(QPersonPetCte.personPetCte.ageSum, QPet.pet.age.sum())