REST-API filter with nested properties in React front end

eraskin · January 26, 2021, 3:06pm

I am getting the following error in my execution of a React load operation using a filtered query:

2021-01-21 17:10:07.012 DEBUG [http-nio-8080-exec-3/pasweb-core/jrusso] com.haulmont.cuba.core.app.RdbmsStore - loadList: metaClass=pasweb_Orders, view=com.paslists.pasweb.entity.Orders/orders-view, query=Query{queryString='select e from pasweb_Orders e where (e.ordType = :UIspqQnNEQ and e.cancelled = :juqOtVGVJf and e.cus.id = :AbyoOWCtpS and e.mplist.brk.id = :zgrcdWfgUd) order by e.bkrnum desc', condition=null, sort=null, firstResult=0, maxResults=10000}, max=10000
2021-01-21 17:10:07.041 ERROR [http-nio-8080-exec-3/pasweb-core/jrusso] com.haulmont.cuba.core.sys.ServiceInterceptor - Exception: 
java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Problem compiling [select e from pasweb_Orders e where (e.ordType = :UIspqQnNEQ and e.cancelled = :juqOtVGVJf and e.cus.id = :AbyoOWCtpS and e.mplist.brk.id = :zgrcdWfgUd) order by e.bkrnum desc]. 
[122, 137] The state field path 'e.mplist.brk.id' cannot be resolved to a valid type.
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1750) ~[org.eclipse.persistence.jpa-2.7.3-14-cuba.jar:na]
	at com.haulmont.cuba.core.sys.QueryImpl.buildJPAQuery(QueryImpl.java:232) ~[cuba-core-7.2.11.jar:7.2.11]
	at com.haulmont.cuba.core.sys.QueryImpl.getQuery(QueryImpl.java:141) ~[cuba-core-7.2.11.jar:7.2.11]
	at com.haulmont.cuba.core.sys.QueryImpl.getResultList(QueryImpl.java:412) ~[cuba-core-7.2.11.jar:7.2.11]
	at com.haulmont.cuba.core.app.RdbmsStore.executeQuery(RdbmsStore.java:901) ~[cuba-core-7.2.11.jar:7.2.11]
	at com.haulmont.cuba.core.app.RdbmsStore.getResultList(RdbmsStore.java:809) ~[cuba-core-7.2.11.jar:7.2.11]
	at com.haulmont.cuba.core.app.RdbmsStore.loadList(RdbmsStore.java:242) ~[cuba-core-7.2.11.jar:7.2.11]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_251]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_251]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_251]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_251]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) [spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) [spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) [spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) [spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) [spring-aop-5.2.9.RELEASE.jar:5.2.9.RELEASE]
	at com.sun.proxy.$Proxy357.loadList(Unknown Source) ~[na:na]

As you can see, it does not like the multi-level parameter ‘e.mplist.brk.id’. Here is the view:

    <view entity="pasweb_Orders" name="orders-view" extends="_local">
        <property name="mplist" view="_minimal" fetch="JOIN">
            <property name="actqty"/>
            <property name="ordqty"/>
            <property name="recdqty"/>
            <property name="mplType"/>
            <property name="status"/>
            <property name="brk" view="_minimal"/>
        </property>
        <property name="totQtyOrdered"/>
        <property name="totQtyConverted"/>
        <property name="cus" view="_minimal" fetch="JOIN"/>
        <property name="lst" view="_minimal" fetch="JOIN"/>
        <property name="mlr" view="_minimal" fetch="JOIN"/>
        <property name="statusDesc"/>
        <property name="totQtyReceived"/>
    </view>

All I need from the “brk” object is the ID, which is already in the mplist entity, hence the “_minimal” view and no fetch parameter on it.

The mplist entity has this field in it:

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "brk_id")
    private Brokers brk;

And, finally, here is the collection definition showing the filter:

      this.dataCollection = collection<Orders>(Orders.NAME, {
        view: "orders-view",
        sort: "-bkrnum",
        filter: {
          conditions: [
            {property: "ordType", operator: "=", value: "MPO"},
            {property: "cancelled", operator: "=", value: "1"},
            {property: "cus", operator: "=", value: cusId},
            {property: "mplist.brk", operator: "=", value: brkId}
          ]
        },
      });

Can anybody tell me why I am getting this state error?

eraskin · January 26, 2021, 9:59pm

I did some more searching. Per this stackoverflow: JPA The state field path cannot be resolved to a valid type, I see this comment:

You can’t use a path expression with a Collection value association. The documentation says: [JPQL Path Expressions (10.2. JPQL Language Reference)
It is syntactically illegal to compose a path expression from a path expression that evaluates to a collection.

My brk object is a collection, so I can’t reference it in a path expression in a filter. I’m thinking the only solution is to create a service that returns the collection I want filtered the way I want it.

Does anybody have a better idea?

v.pushkin · January 27, 2021, 12:03pm

Hi @eraskin,

Your approach should work. I also wasn’t able to reproduce the problem on our test project by creating a similar condition:

dataCollection = collection<DatatypesTestEntity2>(DatatypesTestEntity2.NAME, {
    view: "datatypesTestEntity2-view",
    sort: "-updateTs",
    filter: {
      conditions: [
        {property: 'datatypesTestEntityAttr.associationM2Oattr', operator: '=', value: '<some id>'}
      ]
    }
  });

Could you please create a minimal reproducible example? Perhaps in a fork of the above test project.

eraskin · January 27, 2021, 2:12pm

I think it’s because your attribute is Many to One? I believe my attribute is One to Many? I have:

Orders --<one-to-many>–Mplists–<many-to-one>–Brokers

In other words, one Order has many Mplists. Each Mplist has a Broker. One Broker has many Mplists.

I am trying to filter on the Broker ID (which is a foreign key in the Mplists entity). My goal here is to only show the orders that have at least one Mplist linked to a given Broker.

I will see if I can create the failure case for you.

eraskin · January 27, 2021, 3:13pm

Your sample project doesn’t include a frontend module? I have tried to create one and I keep getting a studio error. Log file shows missing “gradle.properties” file. I re-run build-setup/wrapper to create another one and I make sure to point Gradle to the local wrapper. Clearly I am doing something wrong.

How do I get this project cloned properly?

eraskin · January 27, 2021, 4:09pm

I have successfully created my own minimal example. Please see: cuba-frontend-error

Open the Order List screen and you will get this in the app.log:

Exception Description: Problem compiling [select e from example_Order e where (e.lists.broker.id = :ooEPVSxkvq) order by e.updateTs desc]. 
[37, 54] The state field path 'e.lists.broker.id' cannot be resolved to a valid type.
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:157) ~[org.eclipse.persistence.core-2.7.3-14-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:349) ~[org.eclipse.persistence.core-2.7.3-14-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:280) ~[org.eclipse.persistence.core-2.7.3-14-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:165) ~[org.eclipse.persistence.core-2.7.3-14-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:144) ~[org.eclipse.persistence.jpa-2.7.3-14-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:118) ~[org.eclipse.persistence.jpa-2.7.3-14-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:104) ~[org.eclipse.persistence.jpa-2.7.3-14-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:88) ~[org.eclipse.persistence.jpa-2.7.3-14-cuba.jar:na]
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1748) ~[org.eclipse.persistence.jpa-2.7.3-14-cuba.jar:na]
	... 135 common frames omitted
11:06:28.894 ERROR c.h.a.r.a.c.RestControllerExceptionHandler- Exception in REST controller
com.haulmont.cuba.core.global.RemoteException: 
Exception Description: Problem compiling [select e from example_Order e where (e.lists.broker.id = :ooEPVSxkvq) order by e.updateTs desc]. 
[37, 54] The state field path 'e.lists.broker.id' cannot be resolved to a valid type.

v.pushkin · January 28, 2021, 11:11am

Hi @eraskin,

Thanks!

This use case cannot be implemented by using filters. You’ll need to either create a service or a predefined JPQL query.

eraskin · January 28, 2021, 2:19pm

That’s what I was afraid of. Can you suggest how to use the JPQL query instead? Is this something you put into queries.ts as a “restQuery”?

v.pushkin · January 29, 2021, 7:42am

Yes. Once you have created and exposed your predefined JPQL query via REST API you’ll need to run npm run update-model and a method to execute the query will appear inside the restQuery object.

eraskin · January 29, 2021, 2:14pm

Thank you.

vaplaydevelop · March 24, 2022, 5:04pm

Can you please tell the right way how to use own query from restQuery object?
I have created query in rest-queries.xml, already update model.
queries.ts in react native folder have been succesfull updated to new query.
In react native app I have import { restQueries,} from "../cuba/queries";
But when I try to call qAn = restQueries.MeterLineWorkRep.checkWorkingMeterLineCount there need CubaApp and FetchOptions arguments, but where I can past arguments to query? Like date argument. And how I can get CubaApp argument with current app values?

v.basko · March 25, 2022, 5:28pm

Hi, @vaplaydevelop

Code calling rest query could be looks like

    restQueries.Car.allCars(getCubaREST() as CubaApp)().then(cars => {
      console.log('cars', cars);
    })

Full example of Home screen that is used this code

import * as React from "react";
import { FormattedMessage } from "react-intl";
import {restQueries} from "../../cuba/queries";
import {getCubaREST} from "@cuba-platform/react-core";
import {CubaApp} from "@cuba-platform/rest";

class HomePage extends React.Component {

  render() {

    restQueries.Car.allCars(getCubaREST() as CubaApp)().then(cars => {
      console.log('cars', cars);
    })

    return (
      <div>
        <FormattedMessage id="home.welcome" /> sample-car-rent!
      </div>
    );
  }
}

export default HomePage;

Frontend module for this example is generated using https://github.com/cuba-platform/frontend/tree/master/sample-car-rent back end. Queries described in https://github.com/cuba-platform/frontend/blob/master/sample-car-rent/modules/web/src/com/company/scr/rest-queries.xml

Example of calling query with params will looks like

    restQueries.Car.carsByType(getCubaREST() as CubaApp)({carType: 'HATCHBACK'}).then(cars => {
      console.log('cars', cars);
    })

vaplaydevelop · March 26, 2022, 12:25am

Thank you