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