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
Login
FAQ
Search
