Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: Hibernate, Subquery, Alias
PostPosted: Mon Jun 26, 2017 6:35 am 
Newbie

Joined: Mon Jun 26, 2017 6:27 am
Posts: 1
Hi

I have a query making use of a subquery and would like to both select and order by the subquery:

Code:
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> query = builder.createTupleQuery();
Root<FileStatusBsEntity> from = query.from(FileStatusBsEntity.class);

Subquery subquery = query.subquery(String.class);
Root txFrom = subquery.from(FileStatusTxEntity.class);
Path txtExpr = txFrom.get("text");
subquery.select(txtExpr);

Path pkExpr = txFrom.get("fileStatusTxPK");
Path langExpr = pkExpr.get("langCd");

Predicate joinPred = builder.equal(pkExpr.get("fileStatusCd"), from.get("fileStatusCd"));
Predicate translatePred = builder.equal(langExpr, "en");
subquery.where(builder.and(joinPred, translatePred));

Selection alias = subquery.getSelection().alias("test");
query.multiselect(from.get("fileStatusCd"), alias);
query.orderBy(builder.asc((Expression<?>) alias));



I would have expected a "SELECT (subquery...) as test FROM ... ORDER BY test asc". However, I rather get the subquery twice, once in the from and ones in the ORDERBY, which in turn fails for the ORDERBY.

The generated query looks like:

Code:
select generatedAlias0.fileStatusCd,
(select generatedAlias1.text from ch.adnovum.asap.resource.browser.demo.datamodel.entities.reference.FileStatusTxEntity as generatedAlias1
where ( generatedAlias1.fileStatusTxPK.fileStatusCd=generatedAlias0.fileStatusCd ) and ( generatedAlias1.fileStatusTxPK.langCd=:param0 ))
from ch.adnovum.asap.resource.browser.demo.datamodel.entities.reference.FileStatusBsEntity as generatedAlias0

order by
(select generatedAlias1.text from ch.adnovum.asap.resource.browser.demo.datamodel.entities.reference.FileStatusTxEntity as generatedAlias1
where ( generatedAlias1.fileStatusTxPK.fileStatusCd=generatedAlias0.fileStatusCd )
and ( generatedAlias1.fileStatusTxPK.langCd=:param1 )) asc


And the error I get:

Code:
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: query [select generatedAlias0.fileStatusCd, (select generatedAlias1.text from ch.adnovum.asap.resource.browser.demo.datamodel.entities.reference.FileStatusTxEntity as generatedAlias1 where ( generatedAlias1.fileStatusTxPK.fileStatusCd=generatedAlias0.fileStatusCd ) and ( generatedAlias1.fileStatusTxPK.langCd=:param0 )) from ch.adnovum.asap.resource.browser.demo.datamodel.entities.reference.FileStatusBsEntity as generatedAlias0 order by (select generatedAlias1.text from ch.adnovum.asap.resource.browser.demo.datamodel.entities.reference.FileStatusTxEntity as generatedAlias1 where ( generatedAlias1.fileStatusTxPK.fileStatusCd=generatedAlias0.fileStatusCd ) and ( generatedAlias1.fileStatusTxPK.langCd=:param1 )) asc]
   at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
   at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91)
   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:268)
   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:190)
   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
   at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
   at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
   at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:152)
   at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:521)
   at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:623)
   ... 74 more


Any ideas how to fix that?
Regards Remo


Top
 Profile  
 
 Post subject: Re: Hibernate, Subquery, Alias
PostPosted: Tue Jun 27, 2017 12:14 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1385
You forgot to call correlate between the Root and the SubQuery.

Check out this test on GitHub for more details.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.