JPA Criteria API: select count(*) from subquery with joins and group by for pagination implementation

Question

I want to implement a pagination feature in a list of products returned from database. So I need to known the total results of the query.

Database tables are:

PRODUCT TABLE             
-----------------------------
ID             NAME
-------------- --------------
1              Product 1
2              Product 2

PRODUCT SUPPLIER TABLE
--------------------------------------------
ID             PRODUCT ID     PRICE
-------------- -------------- --------------
1              1              35
2              1              30
3              2              70
4              2              75

The desire result is a list of products with the minimum price of the different suppliers.

Product 1    30
Product 2    70

Main query should be something like:

select p.name, min(ps.price) from product
inner join product_supplier ps on ps.product_id = p.id
group by p.name

So count query should be something like:

select count(*) from (
   select p.name, min(ps.price) from product
   inner join product_supplier ps on ps.product_id = p.id
   group by p.name
)

Code I'am using to get paginated results is:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> tupleQuery = cb.createTupleQuery();

Root<product> product = tupleQuery.from(product.class);
Join<product, productSupplier> productSuppliers = product.join("productSuppliers");

tupleQuery.multiselect(
   product,
   cb.min(productSuppliers.get("price")).alias("price")
);
tupleQuery.groupBy(product);
tupleQuery.orderBy(cb.asc(product.get("name")));

TypedQuery<Tuple> query = entityManager
                            .createQuery(tupleQuery)
                            .setMaxResults(pageable.getPageSize())
                            .setFirstResult(Long.valueOf(pageable.getOffset()).intValue());

List<Tuple> products = query.getResultList();
for (Tuple productResult : products) {
   Product product = productResult.get(product);
   Double minPrice = (Double)productResult.get("price");
   ProductDTO productDTO = new ProductDTO(product, minPrice);  
}

But I'm not sure how to get count from the main query. Code I'm using is:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    
CriteriaQuery<Long> countQuery = cb.createQuery(Long.class)
    
Root<Product> product = countQuery.from(Product.class);
Join<Product, ProductSupplier> productSuppliers = product.join("productSuppliers");
    
countQuery.groupBy(product);
countQuery.select(cb.count(product));

long count = entityManager.createQuery(countQuery).getSingleResult();

But it produces the following SQL:

select
   count(product0_.id) as col_0_0_ 
from
   pu_products product0_ 
inner join
   pu_product_suppliers productsu1_ 
      on product0_.id=productsu1_.product_id
group by
   product0_.id

Returning more than one result, throwing the following exception:

org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 31

So I don't know how to get it work.

Thanks.

Answer 1

Seems that is not possible to do a select count (*) from from an aggregated query via Criteria API:

• Discussion in discourse.hibernate.org
• You can use a subquery only in your WHERE but not in the SELECT or FROM clause

So finally I've applied this solution found in this answer:

• After build the query via Criteria API, get the final query string.
• Wrap it with select count(*) from ({final_query_string}).
• Create a Native Query with the result of wrapping the final query string with the select count (*) from.

Full code of this solution in this answer from @fliX.

Regards.

Answer 2

What about a namedquery like this one :

select count(distinct ps.product) from product_supplier ps where ps.price is not null