Syntax error, QueryDSL "Case When" on a Map<> field, with aggregation

Question

I have an event table and an eventData table linked as a Map<> with Hibernate

event -> Map<> eventDatas

@OneToMany(fetch=FetchType.LAZY)
@JoinColumn(name = "event_id", referencedColumnName = "event_id")
@MapKey(name = "idk.key")
public Map<DATA_KEY, eventData> getEventDatas() {
    return eventDatas;
}

Then I've got this QueryDSL to fetch an aggregate of event grouped by date.

If the Map<> contains a pair DATA_KEY.CODE=202, the group is "OK" otherwise, if that pair is missing or has a different value, the group is "FAIL".

final QEvent event = QEvent.event;

Expression<String> groupCase = 
    event.eventDatas.get(DATA_KEY.CODE).valueInt 
    .when(202).then("OK") 
    .otherwise("FAIL");

ConstructorExpression<StatDto> constructor = 
    Projections.constructor(StatDto.class, event.date, event.count(), groupCase);

query.select(constructor) 
    .from(event) 
    .leftJoin(event.eventDatas) 
    .groupBy(event.date, groupCase) 
    .orderBy(event.date.asc());

return query.fetch();

This query returns the error:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: with near line 2, column 73 
[select event.date, count(event), case when event_eventDatas_0.valeurInt = ?1 then 'OK' else 'FAIL' end
from event.eventDatas as event_eventDatas_0 with key(event_eventDatas_0) = ?2, entitystat.event event
  left join event.eventDatas
group by event.date, case when event.eventDatas.get(?2).valeurInt = ?1 then 'OK' else 'FAIL' end
order by event.date]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:131)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:93)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1836)
    at com.querydsl.jpa.hibernate.DefaultSessionHolder.createQuery(DefaultSessionHolder.java:36)
    at com.querydsl.jpa.hibernate.AbstractHibernateQuery.createQuery(AbstractHibernateQuery.java:104)
    at com.querydsl.jpa.hibernate.AbstractHibernateQuery.createQuery(AbstractHibernateQuery.java:97)
    at com.querydsl.jpa.hibernate.AbstractHibernateQuery.fetch(AbstractHibernateQuery.java:174)

Without groupCase in the query, the error disappears, proving the problem comes from the case itself.

What is the correct syntax to use a Map<> case with an aggregation?

Addendum:

The error points the with token in the from line :

from event.eventDatas as event_eventDatas_0 with key(event_eventDatas_0) = ?2

This is the SQL generated by Hibernate without the groupCase:

select event0_.date as col_0_0_, count(event0_.event_id) as col_1_0_ 
from event event0_ 
group by event0_.date
order by event0_.date

Can you please provide the generated SQL without the groupCase? Thanks! — Bonifacio, Dec 28 '15 at 16:18
No, the issue is still ongoing. Nobody ever found the right way to achieve this correctly. — Guillaume F., May 14 '16 at 20:42

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

In the exception it showed how the sql query is getting executed.

select
event.date,
count(event),
case when event_eventDatas_0.valeurInt = ?1 then 'OK' else 'FAIL' end
from
event.eventDatas as event_eventDatas_0 with key(event_eventDatas_0) = ?2, entitystat.event event left join event.eventDatas
group by event.date, case when event.eventDatas.get(?2).valeurInt = ?1 then 'OK' else 'FAIL' end
order by event.date

Selection shouldn't contain any expression related to Projection refference

the above sql for group by should be group by event.date, alisaName

proper sql statement for your case :
select
event.date,
count(event),
case when event_eventDatas_0.valeurInt = ?1 then 'OK' else 'FAIL' end as aliasName
from
event.eventDatas as event_eventDatas_0 with key(event_eventDatas_0) = ?2, entitystat.event event left join event.eventDatas
group by event.date, aliasName
order by event.date

I haven't tested below code but assuming this should fix it(adding Alias is proper way to fix it).

final QEvent event = QEvent.event;
final QEventData eventData = QEventData.eventData;

String alias="alias";

Expression<String> groupCase = 
event.eventDatas.get(DATA_KEY.CODE).valueInt 
.when(202).then("OK") 
.otherwise("FAIL").as(alias);

ConstructorExpression<StatDto> constructor = 
Projections.constructor(StatDto.class, event.date, event.count(), groupCase);

query.select(constructor) 
.from(event) 
.leftJoin(event.eventDatas,eventData) 
.groupBy(event.date, alias) 
.orderBy(event.date.asc());

return query.fetch();

edited May 23 '17 at 11:52

Community

1
1

answered Dec 25 '15 at 18:37

Pavan Kumar Jorrigala

3,085
16
27

Thank you for the answer. I tested various alternatives based on your example without luck. The error is still there. – Guillaume F. Dec 28 '15 at 08:56
unexpected token: with near line 2, column 77 [select DATE_FORMAT(event.datetime,'%Y-%m-%d %H:00'), count(event), (case when (event_eventData_0.valeurInt = ?1) then '2ok' else '1fail' end) as testAlias from event.eventData as event_eventData_0 with key(event_eventData_0) = ?2, event event left join event.eventData inner join event.logMouvement as m with m.logNoeudOrigine.idk.type = ?3 where event.type = ?4 and event.datetime between ?5 and ?6 group by DATE_FORMAT(event.datetime,'%Y-%m-%d %H:00'), testAlias order by event.datetime desc] – Guillaume F. Dec 28 '15 at 08:57
I think the problem comes from the `from event.eventData as event_eventData_0 with key(event_eventData_0) = ?2, event event`. This is added automatically, but feels wrong. – Guillaume F. Dec 28 '15 at 08:58
I also feels the same, it shouldn't be `event event`, from the earlier exceptions from the post it should be `entitystat.event event left join event.eventDatas`. did you changed your `query.select(constructor) .from(event) ............` – Pavan Kumar Jorrigala Dec 28 '15 at 17:00
Right, that was `entitystat.event event`. I had to shrink the comment because of limited number of characters and I was a bit agressive. – Guillaume F. Dec 28 '15 at 17:07
`event_eventDatas_0.valeurInt = ?1` makes scenes because in groupCase statement we are passing the value `202` for `key(event_eventDatas_0) = ?2` the value is missing in query statement, you can fix by `final QEventData eventData = QEventData.eventData;` and `.leftJoin(event.eventDatas,eventData)` – Pavan Kumar Jorrigala Dec 28 '15 at 18:02
Still no luck, it keeps on adding `from event.eventDatas as event_eventDatas_0 with key(event_eventDatas_0) = ?2` automatically, and the error points the `with` token in this `from` line. – Guillaume F. Dec 28 '15 at 18:17
can you also add the query statement because in sql statement expecting 6 parameters, just want to look the query – Pavan Kumar Jorrigala Dec 28 '15 at 18:24
The query statement wasn't created since Hibernate throws an Exception before reaching that point. The query statement should be exactly what you see in the Exception. I can't see the parameters in the logs.... - The problem clearly comes from the `with`, I don't know how to remove it. – Guillaume F. Dec 28 '15 at 18:32

score 0 · Answer 2 · answered Dec 29 '15 at 16:03

Reading your question I came up with the same conclusion as yours:

The problem clearly comes from the with, I don't know how to remove it

It is probably not even possible to do with Hibernate, my best guess is that it can't predict what value should be used to create a new object when using the CASE WHEN expression inside the query's projection.

You can solve this problem by changing your query projection's constructor to bring the EventData key value and then applying the previous CASE WHEN logic inside the StatDto constructor, like the following:

First, the constructor annotated as @QueryProjection:

class StatDto {

  private Date eventDate;
  private int eventCount;
  private String eventDataStatus;

  @QueryProjection
  public StatDto(Date eventDate, int eventCount, int eventDataKey){
     this.eventDate = eventDate;
     this.eventCount = eventCount;
     if (eventDataKey == 202) {
        this.eventDataStatus = "OK";
     } else {
        this.eventDataStatus = "FAIL";
     }
  }

}

Query projection constructor:

query.from(event) 
    .leftJoin(event.eventDatas) 
    .groupBy(event.date, groupCase) 
    .orderBy(event.date.asc())
    .list(ConstructorExpression.create(StatDto.class, event.date, event.count(), event.eventDatas.get(DATA_KEY.CODE).valueInt));

Note: I don't know if the above approach works exactly like this, if by any means it does not work with the code shown, try changing the code to work using the idea given.

Waiting for your feedback, good luck!

Thanks for your answer. I gave it a try. `@QueryProjection` is handy but doesn't solve the issue (I will use it from now on). Sadly, the aggregation won't be correct if I receive directly the dataKey value, it will create many "fail" DTO instead of only one with the sum. — Guillaume F., Dec 29 '15 at 17:56

Syntax error, QueryDSL "Case When" on a Map<> field, with aggregation

2 Answers2