Weird pagesize in table

Support
#1

Hi,

I use customcollectiondatasource and I am getting weird pagesize in table showing that datasource.

In screen init i use this:


super.init(params);
CustomDs.setMaxResults(50);
CustomDs.setFirstResult(0);

In the screen I have a tree structure, and if I select a node the event on the itemchange fire. In this event run a filter in this customcollectionds and reassign it to the table. Something like this:


productGroupTreeDs.addItemChangeListener(e -> {
    CustomDs.refresh(ParamsMap.of("productGroup",e.getItem()));
    table.setDatasource(CustomDs);
});

It is working fine it is showing the first 50 record in the table, but when i hit the next page link the next page will start from 200 and ends on 250!

And this is consistent so the next and prev link paging the data by 200 record, however the pagesize remains 50.
What can be the cause of this and how can I set the proper pagesize?
Regards

#2

I checked the app.log (Inserted some debug logging into getentities method) and saw that for single next page click the getentities called 4 times.
Why???


2017-02-09 22:12:11.040 INFO  [http-nio-8080-exec-17/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - getAllChildProductGroup 20,0
2017-02-09 22:12:11.041 INFO  [http-nio-8080-exec-17/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - Query: WITH RECURSIVE tree AS (
  SELECT id,ARRAY[]::uuid[] AS ancestorsId
  FROM mascoproto_product_group WHERE id = 'bbd58d5c-5347-8aac-722a-bca156736146' 
  UNION ALL
  SELECT mascoproto_product_group.id, tree.ancestorsId || mascoproto_product_group.parent_id
  FROM mascoproto_product_group, tree
  WHERE mascoproto_product_group.parent_id = tree.id
)
SELECT * FROM mascoproto_arak_xls_import AS A, tree AS T WHERE A.cikk_csoport_id = T.id LIMIT 20 OFFSET 0
2017-02-09 22:12:11.062 INFO  [http-nio-8080-exec-17/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - resultset count:20
2017-02-09 22:12:15.466 DEBUG [http-nio-8080-exec-9/arak24/admin] com.haulmont.cuba.web.App - Ping session
2017-02-09 22:12:44.862 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - getAllChildProductGroup 20,20
2017-02-09 22:12:44.864 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - Query: WITH RECURSIVE tree AS (
  SELECT id,ARRAY[]::uuid[] AS ancestorsId
  FROM mascoproto_product_group WHERE id = 'bbd58d5c-5347-8aac-722a-bca156736146' 
  UNION ALL
  SELECT mascoproto_product_group.id, tree.ancestorsId || mascoproto_product_group.parent_id
  FROM mascoproto_product_group, tree
  WHERE mascoproto_product_group.parent_id = tree.id
)
SELECT * FROM mascoproto_arak_xls_import AS A, tree AS T WHERE A.cikk_csoport_id = T.id LIMIT 20 OFFSET 20
2017-02-09 22:12:44.873 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - resultset count:20
2017-02-09 22:12:45.176 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - getAllChildProductGroup 20,40
2017-02-09 22:12:45.177 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - Query: WITH RECURSIVE tree AS (
  SELECT id,ARRAY[]::uuid[] AS ancestorsId
  FROM mascoproto_product_group WHERE id = 'bbd58d5c-5347-8aac-722a-bca156736146' 
  UNION ALL
  SELECT mascoproto_product_group.id, tree.ancestorsId || mascoproto_product_group.parent_id
  FROM mascoproto_product_group, tree
  WHERE mascoproto_product_group.parent_id = tree.id
)
SELECT * FROM mascoproto_arak_xls_import AS A, tree AS T WHERE A.cikk_csoport_id = T.id LIMIT 20 OFFSET 40
2017-02-09 22:12:45.183 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - resultset count:20
2017-02-09 22:12:45.458 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - getAllChildProductGroup 20,60
2017-02-09 22:12:45.459 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - Query: WITH RECURSIVE tree AS (
  SELECT id,ARRAY[]::uuid[] AS ancestorsId
  FROM mascoproto_product_group WHERE id = 'bbd58d5c-5347-8aac-722a-bca156736146' 
  UNION ALL
  SELECT mascoproto_product_group.id, tree.ancestorsId || mascoproto_product_group.parent_id
  FROM mascoproto_product_group, tree
  WHERE mascoproto_product_group.parent_id = tree.id
)
SELECT * FROM mascoproto_arak_xls_import AS A, tree AS T WHERE A.cikk_csoport_id = T.id LIMIT 20 OFFSET 60
2017-02-09 22:12:45.466 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - resultset count:20
2017-02-09 22:12:45.730 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - getAllChildProductGroup 20,80
2017-02-09 22:12:45.730 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - Query: WITH RECURSIVE tree AS (
  SELECT id,ARRAY[]::uuid[] AS ancestorsId
  FROM mascoproto_product_group WHERE id = 'bbd58d5c-5347-8aac-722a-bca156736146' 
  UNION ALL
  SELECT mascoproto_product_group.id, tree.ancestorsId || mascoproto_product_group.parent_id
  FROM mascoproto_product_group, tree
  WHERE mascoproto_product_group.parent_id = tree.id
)
SELECT * FROM mascoproto_arak_xls_import AS A, tree AS T WHERE A.cikk_csoport_id = T.id LIMIT 20 OFFSET 80
2017-02-09 22:12:45.738 INFO  [http-nio-8080-exec-7/arak24-core/admin] hu.codewizards.mascoproto.service.PersistenceServiceBean - resultset count:20
#3

Hi,

How do you implement your CustomCollectionDatasource? Could your share a small sample?

#4

HI the customCollectionDatasource is the following:


package hu.codewizards.mascoproto.web.datasources;
import com.haulmont.cuba.core.global.AppBeans;
import com.haulmont.cuba.gui.data.impl.CustomCollectionDatasource;
import hu.codewizards.mascoproto.entity.ArakXLSImport;
import hu.codewizards.mascoproto.entity.ProductGroup;
import hu.codewizards.mascoproto.service.PersistenceService;

import java.util.Collection;
import java.util.Map;
import java.util.UUID;

/**
 * Created by dobi on 2017. 02. 02..
 */
public class ArakImportCustomDatasource extends CustomCollectionDatasource<ArakXLSImport, UUID> {

    private PersistenceService persistenceService = AppBeans.get(PersistenceService.NAME);
    private ProductGroup currentProductGroup;

    @Override
    public int getCount() {
        return persistenceService.getAllChildOfProductGroupCount(currentProductGroup);
    }

    @Override
    protected Collection<ArakXLSImport> getEntities(Map params) {
        currentProductGroup = (ProductGroup) params.get("productGroup");
        return persistenceService.getAllChildOfProductGroup(currentProductGroup, maxResults,firstResult);
    }

}

The persistenceService implements two method with special SQL.


@Override
    public List<ArakXLSImport> getAllChildOfProductGroup(ProductGroup group, int maxResults, int firstResult) {
        logger = LoggerFactory.getLogger(PersistenceServiceBean.class);
        logger.info("getAllChildProductGroup " + String.valueOf(maxResults) + "," + String.valueOf(firstResult));
        try(Transaction tx = persistence.createTransaction()){
            EntityManager mgr = persistence.getEntityManager();
            TypedQuery<ArakXLSImport> q = mgr.createNativeQuery("WITH RECURSIVE tree AS (\n" +
                    "  SELECT id,ARRAY[]::uuid[] AS ancestorsId\n" +
                    "  FROM mascoproto_product_group WHERE id = " +"'" + group.getId().toString() + "'" +" \n" +
                    "  UNION ALL\n" +
                    "  SELECT mascoproto_product_group.id, tree.ancestorsId || mascoproto_product_group.parent_id\n" +
                    "  FROM mascoproto_product_group, tree\n" +
                    "  WHERE mascoproto_product_group.parent_id = tree.id\n" +
                    ")\n" +
                    "SELECT * FROM mascoproto_arak_xls_import AS A, tree AS T WHERE A.cikk_csoport_id = T.id LIMIT " + String.valueOf(maxResults) + " OFFSET " + String.valueOf(firstResult),ArakXLSImport.class);
            logger.info("Query: " + q.getQueryString());
            List<ArakXLSImport> l = q.getResultList();
            logger.info("resultset count:" + String.valueOf(l.size()));
            //TODO FIX THIS and implement lazy load in client tier somehow
            for(ArakXLSImport item : l){
                mgr.reload(item, "arakXLSImport-browse-view");
            }
            return l;
        }
    }

    @Override
    public int getAllChildOfProductGroupCount(ProductGroup group) {
        try(Transaction tx = persistence.createTransaction()){
            EntityManager mgr = persistence.getEntityManager();
            Query q = mgr.createNativeQuery("WITH RECURSIVE tree AS (\n" +
                    "  SELECT id,ARRAY[]::uuid[] AS ancestorsId\n" +
                    "  FROM mascoproto_product_group WHERE id = " +"'" + group.getId().toString() + "'" +" \n" +
                    "  UNION ALL\n" +
                    "  SELECT mascoproto_product_group.id, tree.ancestorsId || mascoproto_product_group.parent_id\n" +
                    "  FROM mascoproto_product_group, tree\n" +
                    "  WHERE mascoproto_product_group.parent_id = tree.id\n" +
                    ")\n" +
                    "SELECT count(*) FROM mascoproto_arak_xls_import AS A, tree AS T WHERE A.cikk_csoport_id = T.id");
            return  ((Number) q.getSingleResult()).intValue();
        }
    }

As You can see from the log in my previous post, the persistencesevice method was called 4 times with 4 consecutive page…

#5

Any clue where to search the problem?

#6

Why do use set datasource for Table again after item change in Tree? It is not required since Table already listen to collection change events. Could you try to remove this datasource reassigning code and reproduce the issue?

#7

Hi,

It is because, there is a filter component in this screen too.

The functionality is the following:
Either you can choose a treenode fro ma treelike structure of product groups and all products will be selected which belongs under this subtree (that is that wierd recusive query which i attached) OR You can usee the filter component which is using an another datasource - in this case a normal collectiondatasource - and filter the products as the user want (complex filtering with filtercomponent)

The switch between the two datasources are the following:

If the user click on the treenode the itemchange will fire and will run the code which i attached.
If the user prepare the filter component, then the screen contains a button and when the user clicks this button the table datasource will change over to the filter datasource and I run the filter. BTW can we get an onclick handler to the filter button in this filter component?? Pretty please, it would be very nice and I can get rid of that extra button.

Regards

#8

Currently, Table does not support changing of datasource on the fly, so to fix the issue you can only integrate special parameter to your datasource, a flag that will be used to decide either we load data using service or we load data using usual loading procedure (just call super.loadData). Do not change datasource of the table on the fly - it leads to this weird behaviour.

#9

Hmm that leads to another problem…

How can I access the query which is produced by the filter? Because I need to put this customcollectiondatasource into the middleware where I can run raw SQL query.
So as I see:
The filter will have a dummy datasource, which will be setup according to the user needs.
The user hits the filter search button,
I need extract the query from this dummy datasource, transfer it to the customcollectiondatasource as paramMap and run the query.
Get the resultset , reload the resultset items to include the lazyload entities, pass up the list to the table component.

Is it correct?