Datasource.getCompiledLoadContext seems not be doing what supposed

Support
#1

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

#3

Hi Michael,

Remove the repetitive assignment of the datasource to the filter component in your browse screen:

        customersTable = factory.createComponent(Table.class);
        customersTable.setDatasource(customersDs.getTargetDatasource());
        customersTable.setId("customersTable");
//        filter.setDatasource(customersDs); // this is the cause
        filter.setApplyTo(customersTable);

When you set a datasource to a filter, the filter takes the internal QueryFilter object and then adds conditions on top of it. That’s why they multiply in your example.

Thanks for the interesting use case, we’ll bear it in mind.

#5

Understood and it works fine, thanks @knstvk .

About the use case, I think at some point that would be interesting to have in CUBA this concept implemented somehow.

The GroupTable by itself is a great component but it would be nicely complemented by an “AggregateDatasource” of such kind.

Regarding performance obviously, but also allowing to combine Group By use cases with the power of the Filter component.

In essence, we aim having all our screens displaying aggregates designed this way : Sales, Purchases, Expenses/Budget/Debts/Financials, Stocks, outstanding amounts of any kind, etc…

I won’t say it’s a priority though. We would be much more interested in having header & footer on GroupTable, or a "GroupDataGrid " or the TreeGrid (I know it’s coming) on which we would feed aggregated data.

Michael

#6

Hi

During tests, I saw that filter max results was not being applied to aggregated data source.

LoadContext for a standard datasource is created by CollectionDataSourceImpl.beforeLoadData() which applies firstResults and maxResults to query.

For a value data source this method it not called and the LoadContext retrieved by getCompiledLoadContext() is not setting firstResult and maxResults.

Do you think it makes sense to make getCompiledLoadContext() uses firstResult and maxResults ? So that resulting load context is exactly similar to what would be the one for a standard datasource.

It is just a matter of copying following lines from beforeLoadData() to getCompiledLoadContext() but maybe this is not the contract that you want to give to this method.

Anyway for a value data source, implementing that in overloaded ValueDataSource.getEntities() does the trick.

        if (firstResult > 0)
            q.setFirstResult(firstResult);

        if (maxResults > 0) {
            q.setMaxResults(maxResults);
        }

EDIT: alternative: implements paging directly at the CustomValueDatasource level by scrolling on the result of getEntities()

Michael

#7

Hi Michael,

Probably it would be better if getCompiledLoadContext() used firstResult and maxResults, but I think we shouldn’t change its behavior now as there may be usages of this method. For example, such a change would break TablePrintFormAction which prints the list of entities currently selected in a browser screen.

#8

Agreed, I overlooked that, thanks @knstvk