Hi
I’m working on a datasource component allowing to produce aggregated data using KeyValueEntities but while being able to be filtered using CUBA Filter
component (dont want to lose that !). As explained by @knstvk in another post, currently CustomValueDatasource
cannot be filtered.
Our ultimate goal is to use this datasource for each and every GroupTable
, because we have noticed that by default, the GroupTable
aggregates after applying the rows limit, which can produce wrong results going unnoticed by users.
I’ve reproduced a trimmed down version of the component in the attached project. It contains 2 datasources impl:
-
CustomerAggregateDatasource
is the subject of this post -
CustomerDataSource
is just a GroupDatasource wrapper allowing to spy internals and compare behaviors
In order for the CustomerAggregateDatasource
to be filterable, it behaves like a standard collection datasource for a given persistent Entity
. Then it retrieves the filtered query in getEntities()
through getCompiledLoadContext()
, transform it into a Group By query and build a ValueLoadContext
with it (excerpt below).
@Override
protected Collection<KeyValueEntity> getEntities(Map<String, Object> params) {
// retrieve compiled query & parameters
LoadContext lc = getCompiledLoadContext();
LoadContext.Query query = lc.getQuery();
String q = query.getQueryString();
log.info("query : "+query);
log.info("queryParameters : "+query);
log.info("load context : " + lc.getQuery().getQueryString()+" - params = "+lc.getQuery().getParameters());
// transform query into a group by query
q = q.replace("select e from testcuba3$Customer e", "select e.name, SUM(e.nbOrders) from testcuba3$Customer e")
.replaceAll(" +", " ");
q = q + " group by e.name";
// create value load context with group by query & original parameters
ValueLoadContext vlc = ValueLoadContext.create()
.setProperties(Arrays.asList("name", "nbOrders"));
vlc.setQueryString(q).setParameters(lc.getQuery().getParameters());
log.info("value load context : "+vlc.getQuery().getQueryString()+" - params = "+vlc.getQuery().getParameters());
// load & build meta class
List<KeyValueEntity> list = AppBeans.get(DataManager.class).loadValues(vlc);
targetMetaClass = new KeyValueMetaClass();
targetMetaClass.addProperty(new KeyValueMetaProperty(this.metaClass, "name", String.class));
targetMetaClass.addProperty(new KeyValueMetaProperty(this.metaClass, "nbOrders", Long.class));
return list;
}
From front-end POV It works fine. But there is a nasty hidden issue: each time the Filter is applied, the condition is duplicated. This can be seen in the log while replicating the same use case between standard & aggregate datasource.
Log for CustomerAggregateDatasource, filter applied twice:
2018-06-18 17:28:33.700 INFO [http-nio-8080-exec-1] com.company.testcuba3.web.customer.CustomerAggregateDatasource - query : Query{queryString='select e from testcuba3$Customer e where e.name like :component_filter_name56449 ESCAPE '\'', firstResult=0, maxResults=0}
2018-06-18 17:28:33.700 INFO [http-nio-8080-exec-1] com.company.testcuba3.web.customer.CustomerAggregateDatasource - queryParameters : [COMPONENT : filter.name56449]
2018-06-18 17:28:33.702 INFO [http-nio-8080-exec-1] com.company.testcuba3.web.customer.CustomerAggregateDatasource - load context : select e from testcuba3$Customer e where e.name like :component_filter_name56449 ESCAPE '\' - params = {component_filter_name56449=(?i)%2%}
2018-06-18 17:28:33.702 INFO [http-nio-8080-exec-1] com.company.testcuba3.web.customer.CustomerAggregateDatasource - value load context : select e.name, SUM(e.nbOrders) from testcuba3$Customer e where e.name like :component_filter_name56449 ESCAPE '\' group by e.name - params = {component_filter_name56449=(?i)%2%}
2018-06-18 17:28:33.703 DEBUG [http-nio-8080-exec-1/app-core/admin] com.haulmont.cuba.core.app.RdbmsStore - query: select e.name, SUM(e.nbOrders) from testcuba3$Customer e where e.name like :component_filter_name56449 ESCAPE '\' group by e.name
2018-06-18 17:28:41.143 INFO [http-nio-8080-exec-2] com.company.testcuba3.web.customer.CustomerAggregateDatasource - query : Query{queryString='select e from testcuba3$Customer e where (e.name like :component_filter_name56449 ESCAPE '\' and e.name like :component_filter_name56449 ESCAPE '\')', firstResult=0, maxResults=0}
2018-06-18 17:28:41.143 INFO [http-nio-8080-exec-2] com.company.testcuba3.web.customer.CustomerAggregateDatasource - queryParameters : [COMPONENT : filter.name56449]
2018-06-18 17:28:41.145 INFO [http-nio-8080-exec-2] com.company.testcuba3.web.customer.CustomerAggregateDatasource - load context : select e from testcuba3$Customer e where (e.name like :component_filter_name56449 ESCAPE '\' and e.name like :component_filter_name56449 ESCAPE '\') - params = {component_filter_name56449=(?i)%2%}
2018-06-18 17:28:41.147 INFO [http-nio-8080-exec-2] com.company.testcuba3.web.customer.CustomerAggregateDatasource - value load context : select e.name, SUM(e.nbOrders) from testcuba3$Customer e where (e.name like :component_filter_name56449 ESCAPE '\' and e.name like :component_filter_name56449 ESCAPE '\') group by e.name - params = {component_filter_name56449=(?i)%2%}
2018-06-18 17:28:41.148 DEBUG [http-nio-8080-exec-2/app-core/admin] com.haulmont.cuba.core.app.RdbmsStore - query: select e.name, SUM(e.nbOrders) from testcuba3$Customer e where (e.name like :component_filter_name56449 ESCAPE '\' and e.name like :component_filter_name56449 ESCAPE '\') group by e.name
Log for standard CustomerDatasource:
2018-06-18 17:29:45.478 INFO [http-nio-8080-exec-12] com.company.testcuba3.web.customer.CustomerDatasource - beforeLoadData : select e from testcuba3$Customer e where e.name like :component_filter_name41467 ESCAPE '\' - params{component_filter_name41467=(?i)%2%}
2018-06-18 17:29:45.478 INFO [http-nio-8080-exec-12] com.company.testcuba3.web.customer.CustomerDatasource - query : select e from testcuba3$Customer e
2018-06-18 17:29:45.479 INFO [http-nio-8080-exec-12] com.company.testcuba3.web.customer.CustomerDatasource - queryParameters : [COMPONENT : filter.name41467]
2018-06-18 17:29:45.481 DEBUG [http-nio-8080-exec-12/app-core/admin] com.haulmont.cuba.core.app.RdbmsStore - loadList: metaClass=testcuba3$Customer, view=com.company.testcuba3.entity.Customer/customer-view, query=select e from testcuba3$Customer e where e.name like :component_filter_name41467 ESCAPE '\', max=50
2018-06-18 17:29:47.133 INFO [http-nio-8080-exec-17] com.company.testcuba3.web.customer.CustomerDatasource - beforeLoadData : select e from testcuba3$Customer e where e.name like :component_filter_name41467 ESCAPE '\' - params{component_filter_name41467=(?i)%2%}
2018-06-18 17:29:47.133 INFO [http-nio-8080-exec-17] com.company.testcuba3.web.customer.CustomerDatasource - query : select e from testcuba3$Customer e
2018-06-18 17:29:47.135 INFO [http-nio-8080-exec-17] com.company.testcuba3.web.customer.CustomerDatasource - queryParameters : [COMPONENT : filter.name41467]
2018-06-18 17:29:47.136 DEBUG [http-nio-8080-exec-17/app-core/admin] com.haulmont.cuba.core.app.RdbmsStore - loadList: metaClass=testcuba3$Customer, view=com.company.testcuba3.entity.Customer/customer-view, query=select e from testcuba3$Customer e where e.name like :component_filter_name41467 ESCAPE '\', max=50
Any idea how to remove this duplication ?
testcuba3.zip (118.2 KB)
Best Regards
Michael