Referencing a datasource

Hi
I am trying to select entity with reference to another entity but getting exception.
Here is the example.

I have following Entities
SalesOrder
SalesOrderLines (Composite of SalesOrder referenced by lines
Articles (Association in SalesOrderLines)

In an User Interface, I want to show Articles in the PickerField based on SalesOrder selected in another PickerField.

I am using collectionDatasource for the PickerField Aricles


select e from inteaccgms$Article e
where e.id = :ds$salesOrdersDs.lines.article.id

However, when selecting the SalesOrder while application is running, getting the following exceptions:


java.lang.IllegalArgumentException: You have attempted to set a value of type class java.util.ArrayList for parameter ds_salesOrdersDs_lines_article_id with expected type of class java.util.UUID from query string 
                select e from inteaccgms$Article e
where e.id = :ds_salesOrdersDs_lines_article_id
            .
    at org.eclipse.persistence.internal.jpa.QueryImpl.setParameterInternal(QueryImpl.java:937)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.setParameter(EJBQueryImpl.java:594)
    at com.haulmont.cuba.core.sys.QueryImpl$Param.apply(QueryImpl.java:428)
    at com.haulmont.cuba.core.sys.QueryImpl.getQuery(QueryImpl.java:93)
    at com.haulmont.cuba.core.sys.QueryImpl.getResultList(QueryImpl.java:212)
    at com.haulmont.cuba.core.app.DataManagerBean.executeQuery(DataManagerBean.java:532)
    at com.haulmont.cuba.core.app.DataManagerBean.getResultList(DataManagerBean.java:470)
    at com.haulmont.cuba.core.app.DataManagerBean.loadList(DataMan

You cannot traverse through collection attributes in the “:ds$salesOrdersDs.lines.article.id” expression. Only to-one relations are supported, the parsing mechanism just stops on a non-entity attribute and returns its value (that is why you receive this exception message).

To select Articles, you should collect their IDs into List and write a query like


select e from inteaccgms$Article e
where e.id in :idList

Thanks for your help. Is there any way I can use that query from the datasource definition that we do from the screen design? Alternate option I see it doing this from service. Thanks for your further help.

You cannot do it in screen design, but you can do it in the screen controller using DataManager.
See the example here.

Hi
I have created the listener



        purchaseOrderDs.addItemPropertyChangeListener(e -> {
            if ("salesOrder".equals(e.getProperty())) {
                loadArticles();
              //  loadMaterialRequired();
            }
        });

and the loadArticles()


 public void loadArticles(){

            SalesOrder salesOrder = salesOrderField.getValue();

            // create a LoadContext
            LoadContext<SalesOrderLine> loadContext = LoadContext.create(SalesOrderLine.class)
                    .setQuery(LoadContext.createQuery("select c from inteaccgms$SalesOrderLine c where c.salesOrder.id = :salesOrder")
                            .setParameter("salesOrder", salesOrder.getId()))
                    .setView(View.LOCAL);
            // load list using DataManager
            List<SalesOrderLine> idList = dataManager.loadList(loadContext);

            if (idList.size() > 0) {

                LoadContext<Article> loadContext2 = LoadContext.create(Article.class)
                        .setQuery(LoadContext.createQuery("select e from inteaccgms$Article e where e.id = :idList")
                                .setParameter("idList", idList));
                // load list using DataManager
                List<Article> articleList = dataManager.loadList(loadContext2);

                articleField.setValue(articleList);

            }
            articlesDs.refresh();
        }

getting the following exceptions


java.lang.IllegalArgumentException: You have attempted to set a value of type class java.util.ArrayList for parameter idList with expected type of class java.util.UUID from query string select e from inteaccgms$Article e where e.id = :idList.
	at org.eclipse.persistence.internal.jpa.QueryImpl.setParameterInternal(QueryImpl.java:937)
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.setParameter(EJBQueryImpl.java:594)
	at com.haulmont.cuba.core.sys.QueryImpl$Param.apply(QueryImpl.java:438)
	at com.haulmont.cuba.core.sys.QueryImpl.getQuery(QueryImpl.java:103)
	at com.haulmont.cuba.core.sys.QueryImpl.getResultList(QueryImpl.java:222)
	at com.haulmont.cuba.core.app.DataManagerBean.executeQuery(DataManagerBean.java:590)
	at com.haulmont.cuba.core.app.DataManagerBean.getResultList(DataManagerBean.java:528)
	at com.haulmont.cuba.core.app.DataManagerBean.loadList(DataManagerBean.java:169)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.haulmont.cuba.core.app.DataManagerBean$SecureDataManagerInvocationHandler.invoke(DataManagerBean.java:773)
	at com.sun.proxy.$Proxy268.loadList(Unknown Source)
	at com.haulmont.cuba.core.app.DataServiceBean.loadList(DataServiceBean.java:51)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:74)
	at sun.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
	at com.sun.proxy.$Proxy144.loadList(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:86)
	at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:132)
	at com.sun.proxy.$Proxy19.loadList(Unknown Source)
	at com.haulmont.cuba.client.sys.DataManagerClientImpl.loadList(DataManagerClientImpl.java:54)
	at com.company.gms.gui.purchaseorder.PurchaseOrderEdit.loadArticles(PurchaseOrderEdit.java:103)
	at com.company.gms.gui.purchaseorder.PurchaseOrderEdit.lambda$init$60(PurchaseOrderEdit.java:61)
	at com.company.gms.gui.purchaseorder.PurchaseOrderEdit$$Lambda$94/610448115.itemPropertyChanged(Unknown Source)
	at com.haulmont.cuba.gui.data.impl.AbstractDatasource$ItemListener.propertyChanged(AbstractDatasource.java:333)
	at com.haulmont.chile.core.model.impl.AbstractInstance.propertyChanged(AbstractInstance.java:46)
	at com.company.gms.entity.PurchaseOrder.setSalesOrder(PurchaseOrder.java:105)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.haulmont.chile.core.model.utils.MethodsCache.invokeSetter(MethodsCache.java:58)
	at com.haulmont.chile.core.model.impl.AbstractInstance.setValue(AbstractInstance.java:126)
	at com.haulmont.cuba.core.entity.BaseGenericIdEntity.setValue(BaseGenericIdEntity.java:228)
	at com.haulmont.chile.core.model.impl.AbstractInstance.setValue(AbstractInstance.java:112)
	at com.haulmont.chile.core.model.utils.InstanceUtils.setValueEx(InstanceUtils.java:178)
	at com.haulmont.cuba.web.gui.data.PropertyWrapper.setValue(PropertyWrapper.java:93)
	at com.haulmont.cuba.web.gui.components.WebLookupField$LookupPropertyAdapter.setValue(WebLookupField.java:636)
	at com.vaadin.ui.AbstractField.setValue(AbstractField.java:541)
	at com.vaadin.ui.AbstractSelect.setValue(AbstractSelect.java:729)
	at com.vaadin.ui.AbstractField.setValue(AbstractField.java:473)
	at com.vaadin.ui.ComboBox.changeVariables(ComboBox.java:740)
	at com.haulmont.cuba.web.toolkit.ui.CubaComboBox.changeVariables(CubaComboBox.java:137)
	at com.vaadin.server.communication.ServerRpcHandler.changeVariables(ServerRpcHandler.java:609)
	at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:428)
	at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:274)
	at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:79)
	at com.haulmont.cuba.web.sys.CubaVaadinServletService$CubaUidlRequestHandler.lambda$synchronizedHandleRequest$82(CubaVaadinServletService.java:306)
	at com.haulmont.cuba.web.sys.CubaVaadinServletService$CubaUidlRequestHandler$$Lambda$19/883632795.call(Unknown Source)
	at com.haulmont.cuba.web.sys.CubaVaadinServletService.withUserSession(CubaVaadinServletService.java:187)
	at com.haulmont.cuba.web.sys.CubaVaadinServletService$CubaUidlRequestHandler.synchronizedHandleRequest(CubaVaadinServletService.java:306)
	at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
	at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1409)
	at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:369)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.serviceAppRequest(CubaApplicationServlet.java:242)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:165)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.handleNotFiltered(CubaHttpFilter.java:110)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.doFilter(CubaHttpFilter.java:97)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1526)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1482)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)

Oh… I made a mistake and corrected, it should be “in” instead of" = "in the query.

However, now getting error in this line, 4th line from the last


articleField.setValue(articleList);

the complete error :


java.lang.ClassCastException: java.util.Vector cannot be cast to com.haulmont.cuba.core.entity.Entity
	at com.haulmont.cuba.web.gui.data.OptionsDsWrapper.containsId(OptionsDsWrapper.java:218)
	at com.haulmont.cuba.web.gui.components.WebLookupField$LookupOptionsDsWrapper.containsId(WebLookupField.java:372)
	at com.vaadin.ui.AbstractSelect.setValue(AbstractSelect.java:728)
	at com.vaadin.ui.AbstractField.setValueIgnoreReadOnly(AbstractField.java:458)
	at com.haulmont.cuba.web.gui.components.WebAbstractField.setValue(WebAbstractField.java:152)
	at com.haulmont.cuba.web.gui.components.WebLookupField.setValue(WebLookupField.java:140)
	at com.company.gms.gui.purchaseorder.PurchaseOrderEdit.loadArticles(PurchaseOrderEdit.java:105)
	at com.company.gms.gui.purchaseorder.PurchaseOrderEdit.lambda$init$60(PurchaseOrderEdit.java:61)
	at com.company.gms.gui.purchaseorder.PurchaseOrderEdit$$Lambda$94/1654045551.itemPropertyChanged(Unknown Source)
	at com.haulmont.cuba.gui.data.impl.AbstractDatasource$ItemListener.propertyChanged(AbstractDatasource.java:333)
	at com.haulmont.chile.core.model.impl.AbstractInstance.propertyChanged(AbstractInstance.java:46)
	at com.company.gms.entity.PurchaseOrder.setSalesOrder(PurchaseOrder.java:105)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.haulmont.chile.core.model.utils.MethodsCache.invokeSetter(MethodsCache.java:58)
	at com.haulmont.chile.core.model.impl.AbstractInstance.setValue(AbstractInstance.java:126)
	at com.haulmont.cuba.core.entity.BaseGenericIdEntity.setValue(BaseGenericIdEntity.java:228)
	at com.haulmont.chile.core.model.impl.AbstractInstance.setValue(AbstractInstance.java:112)
	at com.haulmont.chile.core.model.utils.InstanceUtils.setValueEx(InstanceUtils.java:178)
	at com.haulmont.cuba.web.gui.data.PropertyWrapper.setValue(PropertyWrapper.java:93)
	at com.haulmont.cuba.web.gui.components.WebLookupField$LookupPropertyAdapter.setValue(WebLookupField.java:636)
	at com.vaadin.ui.AbstractField.setValue(AbstractField.java:541)
	at com.vaadin.ui.AbstractSelect.setValue(AbstractSelect.java:729)
	at com.vaadin.ui.AbstractField.setValue(AbstractField.java:473)
	at com.vaadin.ui.ComboBox.changeVariables(ComboBox.java:740)
	at com.haulmont.cuba.web.toolkit.ui.CubaComboBox.changeVariables(CubaComboBox.java:137)
	at com.vaadin.server.communication.ServerRpcHandler.changeVariables(ServerRpcHandler.java:609)
	at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:428)
	at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:274)
	at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:79)
	at com.haulmont.cuba.web.sys.CubaVaadinServletService$CubaUidlRequestHandler.lambda$synchronizedHandleRequest$82(CubaVaadinServletService.java:306)
	at com.haulmont.cuba.web.sys.CubaVaadinServletService$CubaUidlRequestHandler$$Lambda$19/993142713.call(Unknown Source)
	at com.haulmont.cuba.web.sys.CubaVaadinServletService.withUserSession(CubaVaadinServletService.java:187)
	at com.haulmont.cuba.web.sys.CubaVaadinServletService$CubaUidlRequestHandler.synchronizedHandleRequest(CubaVaadinServletService.java:306)
	at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
	at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1409)
	at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:369)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.serviceAppRequest(CubaApplicationServlet.java:242)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:165)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.handleNotFiltered(CubaHttpFilter.java:110)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.doFilter(CubaHttpFilter.java:97)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1526)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1482)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)

As I understand, articleField is a LookupPickerField and you want to set the list of options to it.
Then use setOptionsList() or setOptionsMap() methods as explained here: https://doc.cuba-platform.com/manual-6.1/gui_LookupField.html