Image 3 entities: Item, Collection (many-many to items), Ownership (connecting a user to either an Item or a Collection). What I’m trying to do is to load data into a table showing which items a user owns whether by owning the item directly or by owning the collection as a whole. What I’ve tried is:
select coalesce(ownedItem, collItem), coll, ownership.id
from Ownership ownership
left join ownership.item ownedItem
left join ownership.collection coll
left join coll.items collItem
However, it does not like that coalesce function. I’ve also tried translating that coalesce into a CASE statement but that did not work either. It doesn’t seem to like that fact that I am trying to return an entity reference from the coalesce instead of columns, even though both ownedItem and collItem both point to the same table in the end.
Is this intended behavior? If so, any advice on how to write such a query within the constraints of Cuba’s JPQL implementation?
select coalesce(ownPiece, collPiece), coll.title
from chordinate_Ownership own
left join own.piece ownPiece
left join own.collection coll
left join coll.pieces collPiece
where
own.owner = :userId
order by ownPiece.title
I get this exception
Exception Description: Syntax error parsing [select coalesce(ownPiece, collPiece), coll.title
from chordinate_Ownership own
left join own.piece ownPiece
left join own.collection coll
left join coll.pieces collPiece
where
own.owner = :userId
order by ownPiece.title].
[16, 35] The encapsulated expression is not a valid expression.
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1750) ~[org.eclipse.persistence.jpa-2.7.3-6-cuba.jar:na]
at com.haulmont.cuba.core.sys.QueryImpl.buildJPAQuery(QueryImpl.java:229) ~[cuba-core-7.1.3.jar:7.1.3]
at com.haulmont.cuba.core.sys.QueryImpl.getQuery(QueryImpl.java:138) ~[cuba-core-7.1.3.jar:7.1.3]
at com.haulmont.cuba.core.sys.QueryImpl.getResultList(QueryImpl.java:409) ~[cuba-core-7.1.3.jar:7.1.3]
at com.haulmont.cuba.core.app.RdbmsStore.loadValues(RdbmsStore.java:622) ~[cuba-core-7.1.3.jar:7.1.3]
at com.haulmont.cuba.core.app.DataManagerBean.loadValues(DataManagerBean.java:254) ~[cuba-core-7.1.3.jar:7.1.3]
at com.haulmont.cuba.core.app.DataServiceBean.loadValues(DataServiceBean.java:66) ~[cuba-core-7.1.3.jar:7.1.3]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:116) ~[cuba-core-7.1.3.jar:7.1.3]
at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) [spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.sun.proxy.$Proxy212.loadValues(Unknown Source) [na:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:94) [cuba-core-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:154) [cuba-web-7.1.3.jar:7.1.3]
at com.sun.proxy.$Proxy35.loadValues(Unknown Source) [na:na]
at com.haulmont.cuba.client.sys.DataManagerClientImpl.loadValues(DataManagerClientImpl.java:146) [cuba-client-7.1.3.jar:7.1.3]
at com.haulmont.cuba.gui.model.impl.KeyValueCollectionLoaderImpl.load(KeyValueCollectionLoaderImpl.java:95) [cuba-gui-7.1.3.jar:7.1.3]
at com.haulmont.cuba.gui.model.impl.ScreenDataImpl.loadAll(ScreenDataImpl.java:80) [cuba-gui-7.1.3.jar:7.1.3]
at com.haulmont.cuba.gui.model.impl.AnnotationBasedLoaderBeforeShow.loadData(AnnotationBasedLoaderBeforeShow.java:36) [cuba-gui-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.WebScreens.loadDataBeforeShow(WebScreens.java:549) [cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.WebScreens.show(WebScreens.java:437) [cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.WebScreens.showFromNavigation(WebScreens.java:543) [cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.gui.config.MenuItemCommands$ScreenCommand.run(MenuItemCommands.java:335) [cuba-gui-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.SideMenuBuilder$MenuCommandExecutor.accept(SideMenuBuilder.java:264) [cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.SideMenuBuilder$MenuCommandExecutor.accept(SideMenuBuilder.java:249) [cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.gui.components.mainwindow.WebSideMenu$MenuItemImpl.menuSelected(WebSideMenu.java:573) [cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.widgets.CubaSideMenu$1.menuItemTriggered(CubaSideMenu.java:90) ~[cuba-web-widgets-7.1.3.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:153) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:115) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.communication.ServerRpcHandler.handleInvocation(ServerRpcHandler.java:431) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:396) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:260) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:82) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:40) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1577) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:425) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.haulmont.cuba.web.sys.CubaApplicationServlet.serviceAppRequest(CubaApplicationServlet.java:329) ~[cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:215) ~[cuba-web-7.1.3.jar:7.1.3]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[servlet-api.jar:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[catalina.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.19]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-websocket.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.19]
at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:73) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.haulmont.cuba.web.sys.CubaHttpFilter.doFilter(CubaHttpFilter.java:93) ~[cuba-web-7.1.3.jar:7.1.3]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:9.0.19]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200) ~[catalina.jar:9.0.19]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[catalina.jar:9.0.19]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) ~[catalina.jar:9.0.19]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[catalina.jar:9.0.19]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[catalina.jar:9.0.19]
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678) ~[catalina.jar:9.0.19]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[catalina.jar:9.0.19]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[catalina.jar:9.0.19]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) ~[tomcat-coyote.jar:9.0.19]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) ~[tomcat-coyote.jar:9.0.19]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:836) ~[tomcat-coyote.jar:9.0.19]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1747) ~[tomcat-coyote.jar:9.0.19]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-coyote.jar:9.0.19]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_171]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_171]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-util.jar:9.0.19]
at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_171]
Caused by: org.eclipse.persistence.exceptions.JPQLException:
Exception Description: Syntax error parsing [select coalesce(ownPiece, collPiece), coll.title
from chordinate_Ownership own
left join own.piece ownPiece
left join own.collection coll
left join coll.pieces collPiece
where
own.owner = :userId
order by ownPiece.title].
[16, 35] The encapsulated expression is not a valid expression.
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:157) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:336) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:280) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:165) ~[org.eclipse.persistence.core-2.7.3-6-cuba.jar:na]
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:144) ~[org.eclipse.persistence.jpa-2.7.3-6-cuba.jar:na]
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:118) ~[org.eclipse.persistence.jpa-2.7.3-6-cuba.jar:na]
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:104) ~[org.eclipse.persistence.jpa-2.7.3-6-cuba.jar:na]
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:88) ~[org.eclipse.persistence.jpa-2.7.3-6-cuba.jar:na]
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1748) ~[org.eclipse.persistence.jpa-2.7.3-6-cuba.jar:na]
... 88 common frames omitted
2020-01-10 12:10:12.743 ERROR [http-nio-8080-exec-6/chordinate/admin] com.haulmont.cuba.web.log.AppLog - Exception in com.haulmont.cuba.web.widgets.CubaSideMenu:
com.vaadin.server.ServerRpcManager$RpcInvocationException: Unable to invoke method menuItemTriggered in com.haulmont.cuba.web.widgets.client.verticalmenu.CubaSideMenuServerRpc
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:157) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:115) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.communication.ServerRpcHandler.handleInvocation(ServerRpcHandler.java:431) [vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:396) [vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:260) [vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:82) [vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:40) [vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1577) [vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:425) [vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
at com.haulmont.cuba.web.sys.CubaApplicationServlet.serviceAppRequest(CubaApplicationServlet.java:329) [cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:215) [cuba-web-7.1.3.jar:7.1.3]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) [servlet-api.jar:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [catalina.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.19]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat-websocket.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.19]
at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:107) [spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:73) [spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.haulmont.cuba.web.sys.CubaHttpFilter.doFilter(CubaHttpFilter.java:93) [cuba-web-7.1.3.jar:7.1.3]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.19]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [catalina.jar:9.0.19]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [catalina.jar:9.0.19]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [catalina.jar:9.0.19]
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678) [catalina.jar:9.0.19]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [catalina.jar:9.0.19]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [catalina.jar:9.0.19]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-coyote.jar:9.0.19]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-coyote.jar:9.0.19]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:836) [tomcat-coyote.jar:9.0.19]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1747) [tomcat-coyote.jar:9.0.19]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-coyote.jar:9.0.19]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_171]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_171]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:9.0.19]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_171]
Caused by: java.lang.reflect.InvocationTargetException: null
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:153) ~[vaadin-server-8.6.4-23-cuba.jar:8.6.4-23-cuba]
... 38 common frames omitted
Caused by: com.haulmont.cuba.core.global.RemoteException:
Exception Description: Syntax error parsing [select coalesce(ownPiece, collPiece), coll.title
from chordinate_Ownership own
left join own.piece ownPiece
left join own.collection coll
left join coll.pieces collPiece
where
own.owner = :userId
order by ownPiece.title].
[16, 35] The encapsulated expression is not a valid expression.
at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:124) ~[na:na]
at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.1.6.RELEASE.jar:5.1.6.RELEASE]
at com.sun.proxy.$Proxy212.loadValues(Unknown Source) ~[na:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]
at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:94) ~[na:na]
at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:154) ~[cuba-web-7.1.3.jar:7.1.3]
at com.sun.proxy.$Proxy35.loadValues(Unknown Source) ~[na:na]
at com.haulmont.cuba.client.sys.DataManagerClientImpl.loadValues(DataManagerClientImpl.java:146) ~[cuba-client-7.1.3.jar:7.1.3]
at com.haulmont.cuba.gui.model.impl.KeyValueCollectionLoaderImpl.load(KeyValueCollectionLoaderImpl.java:95) ~[cuba-gui-7.1.3.jar:7.1.3]
at com.haulmont.cuba.gui.model.impl.ScreenDataImpl.loadAll(ScreenDataImpl.java:80) ~[cuba-gui-7.1.3.jar:7.1.3]
at com.haulmont.cuba.gui.model.impl.AnnotationBasedLoaderBeforeShow.loadData(AnnotationBasedLoaderBeforeShow.java:36) ~[cuba-gui-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.WebScreens.loadDataBeforeShow(WebScreens.java:549) ~[cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.WebScreens.show(WebScreens.java:437) ~[cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.WebScreens.showFromNavigation(WebScreens.java:543) ~[cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.gui.config.MenuItemCommands$ScreenCommand.run(MenuItemCommands.java:335) ~[cuba-gui-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.SideMenuBuilder$MenuCommandExecutor.accept(SideMenuBuilder.java:264) ~[cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.sys.SideMenuBuilder$MenuCommandExecutor.accept(SideMenuBuilder.java:249) ~[cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.gui.components.mainwindow.WebSideMenu$MenuItemImpl.menuSelected(WebSideMenu.java:573) ~[cuba-web-7.1.3.jar:7.1.3]
at com.haulmont.cuba.web.widgets.CubaSideMenu$1.menuItemTriggered(CubaSideMenu.java:90) ~[cuba-web-widgets-7.1.3.jar:na]
... 43 common frames omitted
I will note that if I instead return an attribute in the coalesce, it is fine. Just not returning entities.
The problem relates to EclipseLink ORM: coalesce function supports only scalar type arguments and doesn’t work with entity variable arguments.
Perhaps additional left join will help with the query, e.g.:
select i, ownership.id from coalescetest_Ownership ownership
left join ownership.item ownedItem
left join ownership.collect coll
left join coll.items collItem
left join coalescetest_Item i ON (i.id = ownedItem.id or i.id = collItem.id)
or
select i, ownership.id from coalescetest_Ownership ownership
left join ownership.item ownedItem
left join ownership.collect coll
left join coll.items collItem
left join coalescetest_Item i ON (i.id = coalesce(ownedItem.id, collItem.id))