How to get Page as result in Querydsl query with fetch or fetchResults properly?

Hi what i trying to achieve here is, i want to submit Pageable data into QueryDsl query and get the result as Page, how can i do it properly? here is what i do until now :

here is my controller :

@PostMapping("/view-latest-stock-by-product-codes")
public ResponseEntity<RequestResponseDTO<Page<StockAkhirResponseDto>>> findStockByProductCodes(
        @RequestBody StockViewByProductCodesDto request) {

    Page<StockAkhirResponseDto> stockAkhir = stockService.findByBulkProduct(request);

    return ResponseEntity.ok(new RequestResponseDTO<>(PESAN_TAMPIL_BERHASIL, stockAkhir));
}

in my controller i submit StockViewByProductCodesDto which is looked like this :

@Data
public class StockViewByProductCodesDto implements Serializable {

    private static final long serialVersionUID = -2530161364843162467L;

    @Schema(description = "Kode gudang yang ingin di tampilkan", example = "GBKTJKT1", required = true)
    private String warehouseCode;

    @Schema(description = "id dari sebuah branch", example = "1", required = true)
    private Long branchId;

    @Schema(description = "Kode Branch", example = "JKT", required = true)
    private String branchCode;

    @Schema(description = "Kode Product yang merupakan kode yang di ambil dari master product", example = "["MCM-508","TL-101"]", required = true)
    private List<String> productCodes;

    @Schema(description = "Size of row per page", example = "15", required = true)
    @NotNull
    private int size;

    @Schema(description = "Page number", example = "1", required = true)
    @NotNull
    private int page;

    @Schema(description = "Sort by", example = "id", required = false)
    private String sort;
}

and here is my service :

public Page<StockAkhirResponseDto> findByBulkProduct(StockViewByProductCodesDto request) {
    String warehouseCode = request.getWarehouseCode();
    Long branchId = request.getBranchId();
    String branchCode = request.getBranchCode();
    List<String> productCodes = request.getProductCodes();
    Set<String> productCodesSet = new HashSet<String>(productCodes);

    Pageable pageable = PageUtils.pageableUtils(request);

    Page<StockAkhirResponseDto> stockAkhir = iStockQdslRepository.findBulkStockAkhirPage(warehouseCode, branchId, branchCode, productCodesSet, pageable);

    return stockAkhir;
}

as you can see, i extract pageable information with PageUtils.pageableUtils(request), here is my pageableUtils function looked like :

public static Pageable pageableUtils(RequestKeyword request) {
    int page = 0;
    int size = 20;

    if (request.getPage() > 0) {
        page = request.getPage() - 1;
    }

    if (request.getSize() > 0) {
        size = request.getSize();
    }

    if (!request.getSort().isEmpty()) {
        return PageRequest.of(page, size, Sort.by(request.getSort()).descending());
    } else {
        return PageRequest.of(page, size);
    }
    
}

after i got the Pageable data, i submit it into my repository, which is looked like this :

public Page<StockAkhirResponseDto> findBulkStockAkhirPage(String warehouseCode, Long branchId, String branchCode,
                                                      Set<String> productCodes, Pageable pageable) {

    JPQLQuery<Tuple> query = new JPAQuery<>(em);

    long offset = pageable.getOffset();
    long limit = pageable.getPageSize();

    QStock qStock = QStock.stock;
    NumberExpression<Integer> totalQty = qStock.qty.sum().intValue();
    query = query.select(qStock.productId, qStock.productCode, totalQty).from(qStock)
            .where(qStock.warehouseCode.eq(warehouseCode), qStock.productCode.in(productCodes),
                    qStock.branchCode.eq(branchCode), qStock.branchId.eq(branchId))
            .groupBy(qStock.productId, qStock.productCode);

    query.limit(limit);
    query.offset(offset);
    QueryResults<Tuple> result = query.fetchResults();
    long total = result.getTotal();
    List<Tuple> rows = result.getResults();

    List<StockAkhirResponseDto> stockAkhirDto = rows.stream()
            .map(t -> new StockAkhirResponseDto(t.get(0, Long.class), t.get(1, String.class), t.get(2, Integer.class)))
            .collect(Collectors.toList());

    return new PageImpl<>(stockAkhirDto, pageable, total);
}

there is no error in my editor when viewing this my repository and i able to run my project, but when i execute my repository function, i got this error :

“org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found ‘,’ near line 1, column 38 [select count(distinct stock.productId, stock.productCode, stock.warehouseId, stock.warehouseCode, stock.branchCode, stock.branchId)nfrom com.bit.microservices.b2b.warehouse.entity.Stock stocknwhere stock.warehouseCode = ?1 and stock.productCode in ?2 and stock.branchCode = ?3 and stock.branchId = ?4]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found ‘,’ near line 1, column 38 [select count(distinct stock.productId, stock.productCode, stock.warehouseId, stock.warehouseCode, stock.branchCode, stock.branchId)nfrom com.bit.microservices.b2b.warehouse.entity.Stock stocknwhere stock.warehouseCode = ?1 and stock.productCode in ?2 and stock.branchCode = ?3 and stock.branchId = ?4]”

the problem is here, on this line :

QueryResults<Tuple> result = query.fetchResults();

when i execute that line, it give me that error, i try to get the fetchResult, because i want to get the .getTotal() for the total.

but if i execute the query with .fetch(), it worked fine, like this :

List<StockAkhirResponseDto> stockAkhirDto = query.fetch()

i got my sql result execute correctly, what did i missed here? how do i get Page result correctly?

Answer

Your problem could be related with an open QueryDSL issue. The documented issue has to do with the use of fetchCount but I think very likely could be also your case.

Consider the following comment in the mentioned issue:

fetchCount() uses a COUNT function, which is an aggregate function. Your query already has aggregate functions. You cant aggregate aggregate functions, unless a subquery is used (which is not available in JPA). Therefore this use case cannot be supported.

The issue also provides a temporary solution.

Basically, the idea is be able to perform the COUNT by creating a statement over the initial select. AFAIK it is not possible with QueryDsl and this is why in the indicated workarounds they access the underline mechanisms provided by Hibernate.

Perhaps, another thing that you can try to avoid the limitation is to create a database view for your query, the corresponding QueryDsl objects over it, and use these objects to perform the actual computation. I am aware that it is not an ideal solution, but it will bypass this current QueryDsl limitation.