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