hibernate - Convert SQL to JPA Specifications

Question

I'm getting a paginated list of product_order using simple CrudRepository with JPA Specification. The problem is building query dynamically with some filters, among other things is product type (not-simple product is produced by at least 3 steps) My SQL:

SELECT * FROM product_order po 
WHERE product_id IN
(
SELECT t.product_id FROM
    (SELECT pp.product_id, count(1) AS cnt FROM product_process pp WHERE pp.version=po.pp_version GROUP BY pp.product_id) t
WHERE t.cnt > 3
)

the count() function is used as a column in a temporary table. So my question is how to use aggregate functions in temporary table

question from:https://stackoverflow.com/questions/66046921/convert-sql-to-jpa-specifications

Answer 1

I'm not sure I understand what you really want, but you can rewrite your query

SELECT *
FROM product_order po 
WHERE EXISTS 
(
  SELECT 1
  FROM product_process pp
  WHERE pp.product_id = po.product_id AND pp.version = po.pp_version
  HAVING count(1) > 3
)

It should be easy to make this work with Spring Data JPA Specifications