Calculate total count for a tuple query using Criteria API?

Question

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)));

Answer 1

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);