I decided to remake the request for the spring jpa specification, I just can’t beat the subqueries in order to implement them on the specification, there is the following query:

SELECT DISTINCT a FROM A a, B b FETCH JOIN a.fieldC FETCH JOIN a.fieldD WHERE a.field=:field and (SELECT COUNT(b) FROM B b WHERE baid=a.id and b.status=STATUS) = 0 and baid=a.id and (SELECT COUNT(b) FROM B b WHERE baid=a.id and b.field.id=1) > 0 and a.date>:date order by a.fieldToOrder desc;

Everything turns out to be done, but as soon as it comes to two subqueries in the query, nothing comes out of me. Can anyone have any ideas? thank

  • Nobody knows how to do this? - Oleg Ivanovskij

1 answer 1

issue resolved with subquery and join. An example for a part of the request, I apologize for not being able to throw off the entire conversion, since it needs to be collected in one code for a long time and replaced with the names of entities - I think using this piece of code it’s clear how to implement the rest of the specification:

 (SELECT COUNT(b) FROM B b WHERE baid=a.id and b.status=STATUS) = 0 Subquery sub = cq.subquery(Long.class); Root subRoot = sub.from(B.class); sub.select(cb.count(subRoot)); sub.where(cb.equal(root.get("ID"), subRoot.get(A).get("ID")), cb.equal(subRoot.get("status"), STATUS)); return cb.equal(sub, 0L);
  • 3
    Could you give a more detailed answer? Thank. - Sasha Chernykh
  • one
    added answer ... - Oleg Ivanovskij