Calculate total count for a tuple query using Criteria API?

user_9630366 0
Viewed 719

I have a Tag table (device_id, customer_id are foreign keys in this table) :

------------------------------------------
| name | value | device_id | customer_id |
------------------------------------------
|   a  |   a   |    10     |    2389     |
------------------------------------------
|   a  |   a   |    20     |    2389     | 
------------------------------------------
|   a  |   a   |    30     |    2389     |
------------------------------------------
|tag-n | tag-v |    10     |    2389     |
------------------------------------------

I am trying to fetch tags with name and value (as search filters) and get back device_id count, name and value.

Example: If I search by name = a and value = a, then response should be:

{
  "customer_id": "2389",
  "tags": [
    {
      "name": "a",
      "value": "a",
      "device_count": 3 //since 3 devices have same name/value pairs
    }
  ],
  "pagination": {
    "offset": 0,
    "page": 0,
    "count_per_page": 1,
    "total_count": 1
  }
}

The Criteria query in my service logic is:

PageRequest pageRequest = (!StringUtils.isBlank(sortBy)) ? PageRequest.of(page, limit, Sort.by(sortBy)) : PageRequest.of(page, limit);

CriteriaBuilder criteriaBuilder =  em.getCriteriaBuilder();
CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
Root<TagEntity> root = query.from(TagEntity.class);
  
// add predicates

Predicate[] predArray = new Predicate[predicates.size()];
predicates.toArray(predArray);

query.multiselect(root.get(TagEntity_.name), root.get(TagEntity_.value), criteriaBuilder.count(deviceJoin.get(DeviceEntity_.ID)))
 .where(predArray).groupBy(root.get(TagEntity_.name), root.get(TagEntity_.value));
                         
TypedQuery<Tuple> typedQuery = em.createQuery(query);

//this works fine
List<Tuple> result = typedQuery
                     .setFirstResult((int) pageRequest.getOffset())
                     .setMaxResults(pageRequest.getPageSize())
                     .getResultList();

//This code fails with InvalidPathException: 'generatedAlias2.customerId'
CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
Root<TagEntity> tagCountRoot = countQuery.from(TagEntity.class);
countQuery.select(criteriaBuilder.count(tagCountRoot)).where(predArray);
Long count = em.createQuery(countQuery).getResultList().get(0);              
Page<Tuple> tagEntities = new PageImpl<>(result, pageRequest, count);

My problem is how do I write the total count query since in my case, I am getting back a Tuple not the entire TagEntity. Any help will be appreciated.

TIA.

P.S. I have created the Predicates in the same method as follows:

List<Predicate> predicates = new ArrayList<>();

Join<TagEntity, DeviceEntity> deviceJoin = root.join(TagEntity_.deviceEntity, JoinType.LEFT);

Join<TagEntity, CustomerEntity> customerJoin = root.join(TagEntity_.customerEntity, JoinType.LEFT);
predicates.add(criteriaBuilder.equal(customerJoin.get(CustomerEntity_.customerId), customerId));

//search by exact name and exact value
predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get(TagEntity_.name), exactName), criteriaBuilder.equal(root.get(TagEntity_.value), exactValue)));
spring-boot criteria-api hibernate-criteria jpa java
2 Answers

You have to always use sorting with paging, even for the default case.

It is simpler to count distinct(name, value) pairs. Don't forget to sort them.

Better to use inner join here

Join<TagEntity, CustomerEntity> customerJoin = root.join(TagEntity_.customerEntity, JoinType.LEFT);
user_34051710

The issue is resolved. I was using the same predicates array defined within the method for both the queries which probably was causing the exception. A better way of doing this is to create a specification as follows:

private Specification<TagEntity> getTagEntitySpecification(String customerId, String name, String value) {
    return (Root<TagEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) -> {
       List<Predicate> predicates = new ArrayList<>();
       
       Join<TagEntity, CustomerEntity> customerJoin = root.join(TagEntity_.customerEntity, JoinType.INNER);
       predicates.add(criteriaBuilder.equal(customerJoin.get(CustomerEntity_.customerId), customerId));

      //search by name and value
      predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get(TagEntity_.name), exactName),
                        criteriaBuilder.equal(root.get(TagEntity_.value), exactValue)));
      
      return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    };
}

Using specification.toPredicate(root, criteriaQuery, criteriaBuilder) in where clause as shown below:

final Specification<TagEntity> specification = getTagEntitySpecification(customerId, name, value);

// query 1
query.multiselect(root.get(TagEntity_.name), root.get(TagEntity_.value),criteriaBuilder.count(deviceJoin.get(DeviceEntity_.ID))).where(specification.toPredicate(root, query, criteriaBuilder)).groupBy(root.get(TagEntity_.name), root.get(TagEntity_.value));

//count query
countQuery.select(cbCount.count(tagCountRoot)).where(specification.toPredicate(tagCountRoot, countQuery, cbCount));
Long count = em.createQuery(countQuery).getResultList().get(0);
tagEntities = new PageImpl<>(result, pageRequest, count);
user_96303660
Related