How to implement this SQL using CriteriaBuilder to build a Predicate?

Question

Im using Specifications that use CriteriaBuilder to filter data which is called from my JpaRepository using findAll(), but now I have a more complicated query in SQL that I need to generate a Predicate in my specification.

The SQL query is:

SELECT a.*
    FROM A a
LEFT JOIN (SELECT e.a_id, sum(e.amount) AS matched_total
    FROM E e
GROUP BY e.a_id
HAVING e.a_id IS NOT NULL) AS sub ON sub.a_id = a.id
WHERE coalesce(matched_total, 0) = a.amount;

Entity E links to A:

+--------+
| id     |
| amount |
| a_id   |
+--------+

Entity A:

+--------+
| id     |
| amount |
+--------+

(Or maybe there is a way to write this as HQL and use that to build a Predicate somehow ?)

Additional notes:

1. There is a one-to-many relationship between E and A, so several rows in A could be associated to a single row in E.
2. The a_id in E is also nullable, so potentially there could be rows in A not associated with a row in E - we need to ignore those.
3. Ultimately we want to find all the rows in A that are associated to a single row in E but only where the amounts of the rows in A sum up to the single amount row in E - this is why we calculate matched_total.

Answer 1

Unless I'm missing something obvious, isn't the query equivalent to the following?

SELECT *
FROM A a
WHERE a.amount > (
    SELECT SUM(e.amount)
    FROM E e
    WHERE e.a_id = a.id
)

The above query is easily translatable to Criteria API, sth along the lines of:

Root<A> a = cq.from(A.class);
Subquery<Integer> sum = cq.subquery(Integer.class);
Root<E> e = sum.from(E.class);
sum.select(cb.sum(e.get(E_.amount));
sum.where(cb.equal(e.join(E_.a), a);

cq.where(cb.greaterThan(a.get(A_.amount), sum);