0

When I fetch all results without pagination result is OK but when use pagination appear problem in pagination metadata

Page p=PageImpl(result.getResults(), PageRequest.of(pageNo, pageSize), result.getTotal()) 
p.getTotalElements() // wrong result
p.result.getTotalPages() // also is wrong

code snippet:-

QOrderAppliedTax qTax = QOrderAppliedTax.orderAppliedTax;
        QOrderDetails qOrderDetails = QOrderDetails.orderDetails;

        NumberExpression<Integer> groupBy = qOrderDetails.createdDate.yearWeek();
        StringPath name = qTax.name;
        NumberExpression<Double> totalTaxValue = qTax.value.sum();
        NumberExpression<Long> ordersCount = qOrderDetails.order.countDistinct();
        DateTimeExpression<Date> fromDate = qOrderDetails.createdDate.min();
        DateTimeExpression<Date> toDate = qOrderDetails.createdDate.max();

        CriteriaBuilderFactory cbf = Criteria.getDefault().createCriteriaBuilderFactory(entityManagerFactory);

        QueryResults<TaxDto> result= new BlazeJPAQuery<>(em, cbf)
                .select(Projections.constructor(TaxDto.class, fromDate, toDate, groupBy, name, totalTaxValue,
                        ordersCount))

                .from(qTax).innerJoin(qOrderDetails).on(qTax.orderDetails.eq(qOrderDetails))

                .groupBy(taxName, groupBy).offset(pageNo * pageSize).limit(pageSize).fetchResults();

the wrong result appears even I used

orderBy(groupBy.asc(), taxName.asc())

exactly the two fields in order by is unique together.

java dev
  • 313
  • 1
  • 3
  • 12
  • What do you mean by "wrong"? What values are you passing in and what values do you get? Also, please post the generated SQL query. – Christian Beikov Sep 24 '21 at 07:24
  • Thanx @Christian Beikov for replying. I grouped by two fileds "taxName, groupBy" the query that brings data is OK but when investigated the count query I found that it "select count(distinct taxName)" taxName only without groupBy so exactly getTotalElements() will be wrong. – java dev Sep 24 '21 at 08:02
  • Could you try `.page(pageNo * pageSize, pageSize)` instead of `offset(pageNo * pageSize).limit(pageSize).fetchResults()`? That uses Blaze-Persistence to generate the pagination instead. I think I might incorrectly pass on the limit and offset to the count query for the `fetchResults` implementation. – Jan-Willem Gmelig Meyling Sep 24 '21 at 09:17
  • I already did that and it is similar to the offset and limit no difference. The total rows are not correct and also total pages. The difference is count query does not appear. – java dev Sep 24 '21 at 09:21
  • What are the used values for `pageNo` and `pageSize`, and the expected and actual results for `getTotalElements` and `getTotalPages`? Could you also provide the produced query (`BlazeJPAQuery.getQueryString`)? I think these are all the info I need to reproduce and fix the issue. – Jan-Willem Gmelig Meyling Sep 24 '21 at 09:24
  • `.page` was supposed to be `.fetchPage` in my previous answer. – Jan-Willem Gmelig Meyling Sep 24 '21 at 09:28
  • How is `taxName` defined? I only see `StringPath name` in the code. Maybe that is the issue? – Christian Beikov Sep 24 '21 at 09:32
  • Comment length is limited so it has two parts. Part1:- ----------- SELECT min(orderDetails.createdDate), max(orderDetails.createdDate), date_format(orderDetails.createdDate,'%Y-%m'), orderAppliedTax.name, sum(orderAppliedTax.value), COUNT(DISTINCT order_1) FROM OrderAppliedTax orderAppliedTax JOIN OrderDetails orderDetails ON (orderAppliedTax.orderDetails = orderDetails) LEFT JOIN orderDetails.order order_1 GROUP BY orderAppliedTax.name, date_format(orderDetails.createdDate,'%Y-%m'), CASE WHEN date_format(orderDetails.createdDate,'%Y-%m') IS NULL THEN 1 ELSE 0 END – java dev Sep 24 '21 at 09:34
  • Part2:- , CASE WHEN orderAppliedTax.name IS NULL THEN 1 ELSE 0 END ORDER BY CASE WHEN date_format(orderDetails.createdDate,'%Y-%m') IS NULL THEN 1 ELSE 0 END, date_format(orderDetails.createdDate,'%Y-%m') ASC, CASE WHEN orderAppliedTax.name IS NULL THEN 1 ELSE 0 END, orderAppliedTax.name ASC – java dev Sep 24 '21 at 09:35
  • Field ```taxName``` is String datatype but I use QueryDSL with Blaze. – java dev Sep 24 '21 at 09:36
  • The queries look ok so far considering that `taxName` and `groupBy` are nullable. How does the count query look like? – Christian Beikov Sep 24 '21 at 09:44
  • You can find the count query string through `BlazeJPAQuery.getFullQueryBuilder(null).getCountQueryString()`. – Jan-Willem Gmelig Meyling Sep 24 '21 at 10:37
  • Feel free to join the Blaze-Persistence and Querydsl slack groups if you run into further issues: https://join.slack.com/t/querydsl/shared_invite/zt-r7ufzz6q-zxIHgpOSSMFvoU3YU4SclQ and https://join.slack.com/t/blazebit/shared_invite/zt-wkbkpx28-xA01ZFLM7UXdp9FfttAQ5A – Jan-Willem Gmelig Meyling Sep 24 '21 at 10:40
  • I can't found ```getFullQueryBuilder(null)``` method. – java dev Sep 24 '21 at 10:41
  • ```taxName``` and ```groupBy``` are nullable. How can I test this they never be null? – java dev Sep 24 '21 at 10:44
  • Why when using ```offset``` and ```limit``` generate this query? select count(distinct orderappli0_.name) as col_0_0_ from order_applied_tax orderappli0_ inner join order_details orderdetai1_ on (orderappli0_.order_details_id=orderdetai1_.id). Note that:- ```count(distinct orderappli0_.name)``` count by name only despite ```groupBy``` name and date – java dev Sep 24 '21 at 10:50
  • It could be because `taxName` is assumed to be unique (for example if its mapped as primary key). If `taxName` is unique and `groupBy` is a property on the same query root, there's would be no need to consider the additional `groupBy` alias. However, these properties come from different query roots, so I suspect this is a count query generation issue in Blaze-Persistence. I'll try to create a reproducer there, so we can look into it further. (https://github.com/Blazebit/blaze-persistence/pulls) – Jan-Willem Gmelig Meyling Sep 24 '21 at 11:29
  • No ```taxName```is not unique it is repeatable. – java dev Sep 24 '21 at 11:31
  • 1
    Ideally, you would mark the columns as "nullable = false" or use `coalesce(.., someConstant)` to avoid these "additional" order by and group by items that handle nullability. – Christian Beikov Sep 24 '21 at 12:31

1 Answers1

1

This issue turned out to be a bug which will be resolved in Blaze-Persistence 1.6.3 (expected soon). The patch has already landed in 1.6.3-SNAPSHOT.

Jan-Willem Gmelig Meyling
  • 3,685
  • 2
  • 21
  • 43