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.

why do not use JPQL/Criteria API? `select p from Person p where (select sum(pp.age) from Pet pp where pp.personId = p.id) > 30` — Andrey B. Panfilov, Jan 29 '23 at 03:56
@AndreyB.Panfilov Thank you for the advice. But I am looking for a solution using Blaze + QueryDsl or QueryDsl all alone because in the real situation I have a project purely built on QueryDsl with complicated queries. Switching to Criteria API would mean a total rewrite. — pterogum, Jan 29 '23 at 06:22

score 1 · Accepted Answer · answered Jan 30 '23 at 12:29

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

answered Jan 30 '23 at 12:29

Jan-Willem Gmelig Meyling

3,685
2
21
43

It seems we are using different versions. On blaze-persistence 1.6.8 I can't get a bind() out of new BlazeJpaQuery() based on your code. – pterogum Jan 31 '23 at 01:45
There is only one version of BlazeJPAQuery and its the version I wrote. See the example here: https://github.com/Blazebit/blaze-persistence/blob/main/integration/querydsl/testsuite/src/test/java/com/blazebit/persistence/querydsl/BasicQueryTest.java#L670 – Jan-Willem Gmelig Meyling Jan 31 '23 at 08:11
yeah bind() is the key part to solving my problem :) – pterogum Feb 02 '23 at 01:36

Blaze Persistence & querydsl join subquery with CTE projections

1 Answers1