Value Datasource JPQL query demo$Order to aggregate/sum daily amount (without time portion)

Support
#1

Hi there,
With the help of the cuba team, I managed to get the Value Datasource working (Thank you!).
My valueCollectionDatasource JPQL is:


        select o.create_ts, sum(o.amount) from demo$Order o group by o.create_ts

Here are 3 order records in demo$Order table:


create_ts	                                amount
2017-03-02 20:01:06.996 	186.00
2017-03-03 19:25:46.677        150.00
2017-03-03 19:59:31.578         100.00

I wanted to get the total order amount grouped by day without timestamp like this:


2017-03-02   	186.00
2017-03-03          250.00

The above JPQL gives me 3 records exactly the same as in the order table (no aggregation grouped by date at all).
My valueCollectionDatasource is:


       <valueCollectionDatasource id="salesAggregatorDs">
            <query>
                <![CDATA[select o.create_ts, sum(o.amount) from demo$Order o group by o.create_ts]]>
            </query>
            <properties>
                <property datatype="date"
                          name="orderDate"/>
                <property datatype="decimal"
                          name="sum"/>
            </properties>
        </valueCollectionDatasource>

Also how to specify the format “yyyy-mm-dd” to property “orderDate” ?
Thanks advance for your help,
-Mike

#2

Hi Mike,

The createTs system attribute contains time part, so you have grouping by date and time, not only by date. I would suggest adding a special attribute of type Date and use it for grouping.

#3

Hi Konstantin,

I followed your suggestion by adding a special attribute of type Date: orderDate for grouping, it works great with this query:

select o.orderDate, sum(o.amount) from demo$Order o group by o.orderDate ORDER BY o.orderDate

Now I need to add date range to the above query. i.e., there must be a way to specify the start and end Date parameters. I did the following and got a nullPointerException: (salesAggregatorDs is the Value Datasource, todayDate is correctly assigned. This example tried to get the daily sum(o.amount) of the last 7 days )


        LoadContext lc = salesAggregatorDs.getCompiledLoadContext();
        lc.setQueryString("select o.orderDate, sum(o.amount) from demo$Order o where (o.orderDate between :start and :end) group by o.orderDate ORDER BY o.orderDate")
                .setParameter("start", DateUtils.addDays(todayDate, -7))
                .setParameter("end", DateUtils.addDays(todayDate, 1));

        salesAggregatorDs.refresh();

The statement of “salesAggregatorDs.refresh();” asserts nullPointerException:


java.lang.NullPointerException: null
        at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:127)
        at sun.reflect.GeneratedMethodAccessor265.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
        at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
        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:213)
        at com.sun.proxy.$Proxy210.loadValues(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:498)
        at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:95)
        at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:146)
        at com.sun.proxy.$Proxy26.loadValues(Unknown Source)
        at com.haulmont.cuba.client.sys.DataManagerClientImpl.loadValues(DataManagerClientImpl.java:137)
        at com.haulmont.cuba.gui.data.impl.GenericDataSupplier.loadValues(GenericDataSupplier.java:80)
        at com.haulmont.cuba.gui.data.impl.ValueDatasourceDelegate.loadData(ValueDatasourceDelegate.java:78)
        at com.haulmont.cuba.gui.data.impl.ValueCollectionDatasourceImpl.loadData(ValueCollectionDatasourceImpl.java:83)
        at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:146)
        at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:106)
        at cloud.pospro.posportal.web.screens.Sales.onLinkBtnLast7DaysClick(Sales.java:239)
        ... 61 more

What’s the correct way to query a Date range in this case?

Thank you,
-Mike

#4

Hi Mike,

The exception is probably caused not by JPQL but by the way you are trying to modify the datasource query and parameters. The getCompiledLoadContext() method is not designed for this purpose, as stated in its JavaDoc.

See an example of modifying the datasource query here. In JPQL, you can use parameters with param$, component$ or cuatom$ prefix, see the explanation in the docs. In the latter case, you need to invoke refresh() in the controller’s code.

Let me know if you have difficulties using this approach for value datasources.

#5

Hi Konstantin,

Thank you very much for your solution. It helps a lot.

Here is the code that specifies the date range of last 30 days with the Value Datasource:


salesAggregatorDs.refresh(ParamsMap.of("startDate", DateUtils.addDays(todayDate, -30), "endDate", DateUtils.addDays(todayDate, 1)));

Again thanks for the great help,
-Mike

#6

Hi Konstantin,

I am quite happy that Value Datasource works and date range parameters can be specified by Ds.refresh(…).

For curiosity, I would like to understand more about the aggregation GROUP BY flexibility on Cuba platform. Is GROUP BY the “YEAR, MONTH, DAY, HOUR, MINUTE, SECOND” of a dateTime type possible on Cuba?

The suggestion of creating a separate column of type Date seems to be a workaround than a solution(it works, thank you!). From related discussions on StackOverflow, JPQL does have this capability. For example, eclipselink has EXTRACT function: “Use EXTRACT to retrieve the date portion of a date/time value.”
Java Persistence Query Language Extensions | EclipseLink 2.6.x Java Persistence API (JPA) Extensions Reference

However, when I use it to group the orders by HOUR like this:


select o.createTs, sum(o.amount) from demo$Order o where (o.orderDate between :custom$startDate and :custom$endDate) group by EXTRACT(HOUR, o.createTs)

The error occurs:


Caused by: com.haulmont.cuba.core.sys.jpql.JpqlSyntaxException: Errors found for input jpql:[select o.createTs, sum(o.amount) from posportal$Order o where (o.orderDate between :custom_startDate and :custom_endDate) group by EXTRACT(HOUR, o.createTs) ORDER BY o.createTs]
CommonErrorNode [<unexpected: [@57,148:155='EXTRACT(',<100>,1:148], resync=EXTRACT(HOUR>]
        at com.haulmont.cuba.core.sys.jpql.Parser.checkTreeForExceptions(Parser.java:99)
        at com.haulmont.cuba.core.sys.jpql.Parser.parse(Parser.java:40)
        at com.haulmont.cuba.core.sys.jpql.QueryTreeAnalyzer.prepare(QueryTreeAnalyzer.java:54)
        at com.haulmont.cuba.core.sys.jpql.QueryTreeAnalyzer.prepare(QueryTreeAnalyzer.java:45)
        at com.haulmont.cuba.core.global.QueryParserAstBased.getQueryAnalyzer(QueryParserAstBased.java:78)
        at com.haulmont.cuba.core.global.QueryParserAstBased.getParamNames(QueryParserAstBased.java:96)
        at com.haulmont.cuba.gui.data.impl.AbstractCollectionDatasource.createDataQuery(AbstractCollectionDatasource.java:524)
        at com.haulmont.cuba.gui.data.impl.ValueDatasourceDelegate.beforeLoadValues(ValueDatasourceDelegate.java:90)
        at com.haulmont.cuba.gui.data.impl.ValueDatasourceDelegate.loadData(ValueDatasourceDelegate.java:73)
        at com.haulmont.cuba.gui.data.impl.ValueCollectionDatasourceImpl.loadData(ValueCollectionDatasourceImpl.java:83)
        at com.haulmont.cuba.gui.data.impl.CollectionDatasourceImpl.refresh(CollectionDatasourceImpl.java:146)
        at cloud.pospro.posportal.web.screens.Sales.init(Sales.java:89)
        at com.haulmont.cuba.gui.WindowManager.init(WindowManager.java:1043)
        at com.haulmont.cuba.gui.WindowManager.initWrapperFrame(WindowManager.java:1032)
        at com.haulmont.cuba.gui.WindowManager.createWindow(WindowManager.java:423)
        at com.haulmont.cuba.gui.WindowManager.openWindow(WindowManager.java:591)
        at com.haulmont.cuba.web.WebWindowManager.openWindow(WebWindowManager.java:137)
        at com.haulmont.cuba.gui.WindowManager.openWindow(WindowManager.java:615)
        at com.haulmont.cuba.gui.components.WindowDelegate.openWindow(WindowDelegate.java:230)
        at com.haulmont.cuba.web.gui.WebWindow.openWindow(WebWindow.java:424)
        at com.haulmont.cuba.gui.components.AbstractFrame.openWindow(AbstractFrame.java:454)
        at cloud.pospro.posportal.web.mainwindow.ExtAppMainWindow.onSalesClick(ExtAppMainWindow.java:85)
        ... 59 more

Does cuba platform support GROUP BY “YEAR, MONTH, DAY, HOUR, SECOND”, or it is something else that cause this problem? If Cuba supports, what is the correct method to specify GROUP BY clause?

Thank you,
-Mike

#7

Mike,

CUBA has its own parser of JPQL which is needed for analyzing and transforming queries before sending them to EclipseLink. Unfortunately, not all features of EclipseLink’s JPQL are correctly handled at the moment. We’ll try to implement EXTRACT function in the near future - thank you for reporting the problem. You can track the progress in the linked YouTrack issue.

1 Like
#8

Great. Looking forward to seeing EXTRACT implementation in Cuba very soon.

With this simple EXTRACT approach, I believe Chart via Value Datasource, the performance should be much improved and controller resources usage (CPU/memory) will be a lot less, since we do not need huge amount of data to be returned to controller for the processing which can be done on database engine itself. Definitely it is a great feature, hope to see it in reality SOON.

Thanks a lot for the help,
-Mike

1 Like
#9

EXTRACT support would be huge help. +1

#10

On a second thought, an enhanced EXTRACT function with more flexibility might be more efficient and helpful.

The standard EXTRACT function looks like:

group by EXTRACT(HOUR, o.createTs)

Here “HOUR” could be “YEAR”, “MONTH”, “DAY”, “MINUTE”, “SECOND”, “MILLISECOND” …

in real application, the need to group order records might not be one of the above. i.e., the application wants to group by “Half hour(30 minutes)”, or “10Minutes”, or “5Second” instead of group by “HOUR”, “MINUTE”, “SECOND”. Thus if (MINUTE, o.createTs) could be designed to be more flexible that allow user to specify a value like:
group by EXTRACT(MINUTE, o.createTs, 10) <<<<< 10 is the value that user can specify. It means group every 10 minutes

This would be a great enhancement for more efficient application.

Just bring this up for Cuba team to consider when implement it if possible.

Thanks,
-Mike

#11

Hi Mike,

Since the platform version 6.5.0, the EXTRACT function is supported.

Regards.

1 Like
#12

:ticket: See the following issue in our bug tracker:

https://youtrack.cuba-platform.com/issue/PL-8781