2

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.
Eno
  • 10,730
  • 18
  • 53
  • 86

1 Answers1

0

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);
crizzis
  • 9,978
  • 2
  • 28
  • 47
  • I added some notes above because I realized there's some missing crucial information. Also note I have changed the original query to `=` in the final `WHERE` clause. We want to ignore rows that don't add up to `matched_total`s. – Eno Oct 25 '20 at 23:25
  • I still fail to see the difference. Of course the rows in E whose `a_id` is null are not included - that's what the `WHERE e.a_id = a.id` clause is for. Could you perhaps provide an example where you think the queries return different results? (assuming `>` is replaced with `=`) – crizzis Oct 26 '20 at 13:56
  • Sorry in the delay in getting back to this - I have been off for a few days and have not been able to test this. Is there a way to do this without using the Metamodel API? – Eno Nov 03 '20 at 21:18