0

There are two tables:

Contact
   id
   firstName
   accountId
Account
   id
   name

I need to count the overall number of contacts grouped by account name. I'm creating a Pageable web service, the total number of records is needed to calculate the total number of pages.

So the SQL will be something like this:

SELECT COUNT(*) FROM (
    SELECT COUNT(a.name)
    FROM contact c
    LEFT JOIN account a ON c.accountId = a.id
    GROUP BY a.name
) AS tmp

How to build a query using the CriteriaBuilder?

The following code returns a list of Long values, e.g. [1, 2, 1]

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<Contact> from = cq.from(Contact.class);

Join<Contact, Account> accJoin = from.join("account", JoinType.LEFT);
cq.select(cb.count(from));
cq.groupBy(accJoin.get("name"));
System.out.println("count: " + entityManager.createQuery(cq).getResultList());

Obviously, it executes the following SQL:

SELECT COUNT(a.name)
FROM contact c
LEFT JOIN account a ON c.accountId = a.id
GROUP BY a.name

But I need to get the overall count of records returned by the above query.

DrWebber
  • 1
  • 3
  • from the [post](https://stackoverflow.com/a/12076584/1211547) it seems like query has to be rewritten so that the outer most query has to reference a specific table to be able to use criteria api – indybee Jan 23 '22 at 16:49
  • 1
    @indybee thanks, but seems that mentioned by you method won't work (since I'm using GROUP BY). Anyway, [this](https://stackoverflow.com/a/66323540/8264653) answer describes how to automatically convert the query to native count query. It helped me a lot. – DrWebber Jan 24 '22 at 10:11

0 Answers0