Re: QuerySyntaxException on querying distinct values from a @ElementCollection Map

[Timo Westkämper]

On Thursday, February 7, 2013 3:41:32 PM UTC+2, (unknown) wrote:

Hi,

I fail to spot if it is an entity bad design or a wrong use of querydsl.

The goal is to return the distinct values so to fill combos in a grid filtering bar where it will be used to build on the fly a query to get the selected values.
It can be huge so the idea is to use the DB as it best to get distinct values to build a subsequent query. 
I don't want to use guava to filter where I think a carefully crafted DB query will be more efficient.

As a work around, I use a native query.
But I'm disappointed I cannot have queryDsl working to keep the rest of our codebase consistent.

---------------------------------
public enum Key {
   k1,k2,k3,k4;
}

@Entity
class A {
   @Id @GeneratedValue(strategy = GenerationType.AUTO)
   private Long id;

   @ElementCollection(fetch=FetchType.EAGER)
   @CollectionTable(name = "A_COLL")
   @MapKeyEnumerated(EnumType.ORDINAL)
   @Column(length=4096)
   protected Map<Key, String>  coll ;
  
   protected A(){
   }
   //getters and setters omitted for brevity
   //...
}

----------------------------------
//Spring Data JPA works
Iterable<A> list = aRepository.findAll(QA.a.coll.containsKey(Key.k1));

//native query works
Query nativeQuery = entityManager.createNativeQuery("SELECT distinct coll FROM A_COLL WHERE key_KEY ="+ Key.k1.ordinal());
List<String> list = nativeQuery.getResultList();

//this querydsl fail
JPAQuery query = new JPAQuery(entityManager);
query.from(QA.a).where(QA.a.coll.containsKey((Key.k1));
List<String> list11 = query.distinct().list(QA.a.coll.get(Key.k1));

 

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: [ near line 1, column 23
[select distinct a.coll[?1] from A a where ?1 in indices(a.coll)]

Could you open a ticket for this exception?
 

--------------------------------
stacktrace

20:25:05.341 [http-bio-8080-exec-10] ERROR o.h.hql.internal.ast.ErrorCounter - <AST>:1:39: unexpected AST node: [
20:25:05.345 [http-bio-8080-exec-10] ERROR o.h.hql.internal.ast.ErrorCounter - <AST>:1:39: unexpected AST node: [
antlr.NoViableAltException: unexpected AST node: [
   at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2030) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1451) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:571) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:299) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:247) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:248) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:105) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:221) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:199) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1735) [hibernate-core-4.1.9.Final.jar:4.1.9.Final]
   at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) [hibernate-entitymanager-4.1.9.Final.jar:4.1.9.Final]
   at sun.reflect.GeneratedMethodAccessor77.invoke(Unknown Source) ~[na:na]
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_09]
   at java.lang.reflect.Method.invoke(Method.java:601) ~[na:1.7.0_09]
   at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:366) [spring-orm-3.2.1.RELEASE.jar:3.2.1.RELEASE]
   at $Proxy89.createQuery(Unknown Source) [na:na]
   at sun.reflect.GeneratedMethodAccessor77.invoke(Unknown Source) ~[na:na]
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_09]
   at java.lang.reflect.Method.invoke(Method.java:601) ~[na:1.7.0_09]
   at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:241) [spring-orm-3.2.1.RELEASE.jar:3.2.1.RELEASE]
   at $Proxy89.createQuery(Unknown Source) [na:na]
   at com.mysema.query.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:134) [querydsl-jpa-2.9.0.jar:na]
   at com.mysema.query.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:103) [querydsl-jpa-2.9.0.jar:na]
   at com.mysema.query.jpa.impl.AbstractJPAQuery.list(AbstractJPAQuery.java:271) [querydsl-jpa-2.9.0.jar:na]
   ....

[Timo Westkämper]

Hi.

The problem here is that Querydsl does not yet support this JPQL syntax which would make this query possible

    select a from Show s join s.acts a where key(a) = 'B'

The problem here is that I can't put a key method into general expressions, since I don't have the key type in the paths.

Here are some alternatives I came up with

    query().from(show).join(show.acts, act, "B").list(act);

or

    query().from(show).join(show.acts, "B", act).list(act);

or
 
    // this could be internally transformed into the expanded form
    query().from(show).list(show.acts.get("B"));

Or do you have other suggestions?

Br,
Timo

   

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[dilber...@gmail.com]

Hi Timo,

Actually it is not the first time I have such problems with @ElementCollection objects.
Most of the time running out of time, I got around it changing the prototype by implementing an embedded or a full blown entity.
This time I cannot see a workaround at the entity level.

Being not literate in jpql, I rely on your library and JPA to hide this nitty-gritty DB dependent sql writing.
To answer your question, I do not have suggestions currently from the top of my head, previous tests were with some subqueries and other failed queries.
I'll find some time to try your alternatives.

Thank you for your diligence,

[Timo Westkämper]

Hi.

The alternatives are syntax variantas that _could_ be implemented.

The only workaround that works now is use the top level entity in the projection.
 

Thank you for your diligence,

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com