In querydsl, how is a subquery performed in the FROM clause?

Question

I have this query:

select gf_storage_zone_type,
       sum(numObjectPhysicalName) numObjects
from (
         select gf_storage_zone_type,
                count(distinct gf_object_physical_name) as numObjectPhysicalName
         from t_kgov_kpi_streaming_object
         group by gf_storage_zone_type,
                  gf_object_physical_name
         order by gf_storage_zone_type
     ) as v
group by gf_storage_zone_type

I am trying to use QueryDSL in Java with Spring, where one of my methods contains:

NumberPath<Long> numObjectPhysicalName = Expressions.numberPath(Long.class, "numObjectPhysicalName");
NumberPath<Long> numObjects = Expressions.numberPath(Long.class, "numObjects");
QKpiRuleBoard qKpiRuleBoard = QKpiRuleBoard.kpiRuleBoard;
List<ObjectsByStorageZoneProjection> objectsByStorageZone = jpaQueryFactory
                .select(Projections.constructor(ObjectsByStorageZoneProjection.class,
                        qKpiRuleBoard.storageZoneType,
                        numObjectPhysicalName.sum().as(numObjects)))
                .from(JPAExpressions
                        .select(
                                Projections.constructor(ObjectsByStorageZoneProjection.class,
                                qKpiRuleBoard.storageZoneType,
                                qKpiRuleBoard.objectPhysicalName.countDistinct().count().as(numObjectPhysicalName)))
                        .from(qKpiRuleBoard)
                        .where(
                                qKpiRuleBoard.cutoffDate.eq(cutoffDate)
                                        .and(qKpiRuleBoard.countryId.eq(countryId))
                                        .and(qKpiRuleBoard.executionFrequencyType.eq(executionFrequencyType)))
                        .groupBy(
                                qKpiRuleBoard.storageZoneType,
                                qKpiRuleBoard.objectPhysicalName))
                .groupBy(qKpiRuleBoard.storageZoneType)
                .fetch();

Throwing the following exception -> com.querydsl.jpa.impl.JPAQuery cannot be cast to com.querydsl.core.types.EntityPath

What can I do to solve it?

score 0 · Answer 1 · answered Aug 24 '21 at 21:00

Usually your query will create an inline view given a subquery is being used in the from clause.

QueryDSL works with JPA behind the scene, and for now, they do not support subqueries in the from clause based on the docs.

Support for subqueries in the FROM clause will be considered in a later release of the specification.

You will have to rewrite your query to adapt it to JPA or use native query.

I believe you will achieve the same result using the query below.

select
    gf_storage_zone_type,
    count(distinct gf_object_physical_name) as numObjects
from
    t_kgov_kpi_streaming_object
group by
    gf_storage_zone_type
order by
    gf_storage_zone_type;

This can easily be translated to JPA.

In querydsl, how is a subquery performed in the FROM clause?

1 Answers1