1

I have a query that is used to order messages by the total number of errors. Every message can have multiple instances. In this case I want to order by the total number of instances which have plausibility or integrity errors.

SELECT *
FROM t_message m
LEFT JOIN (
  SELECT m.id, COUNT(*) AS count
  FROM t_message m
  LEFT JOIN t_alteration_instance i ON i.c_message_id = m.id
  WHERE i.c_current_step_status = "INTEGRITY_ERROR" OR 
    i.c_current_step_status= "PLAUSIBILITY_ERROR"
  GROUP BY m.id
) AS cnt ON cnt.id = m.id
ORDER BY count DESC

This query works in SQL, but how can I translate this to the criteria API, how can I join two queries or is there any other way?

Tomas F
  • 7,226
  • 6
  • 27
  • 36
Yev Slipenko
  • 11
  • 1
  • Does this answer your question? [Is it possible to use raw SQL within a Spring Repository](https://stackoverflow.com/questions/15948795/is-it-possible-to-use-raw-sql-within-a-spring-repository) – Tomas F Aug 11 '21 at 10:05
  • It doesn't, I wanted to avoid using raw query because of the amount of where clauses that are dynamic that need to be put there. And since the rest is done with Criteria I wanted to do this as well – Yev Slipenko Aug 11 '21 at 17:09

0 Answers0