JPQL find entity by exact array match

Question

I have entity Monitor that contains attribute categories which is Set of entity Category. Is it possible to use JPQL to find entity Monitor by Set of Categories? Something like this:

@Query("SELECT m from Monitor m where m.categories = :cats")
Monitor findByCategories(@Param("cats") Set<MessageCategory> cats);

This snippet throws an error com.impossibl.postgres.jdbc.PGSQLSimpleException: syntax error at or near "."

Thanks in advance

Answer 1

If your Monitor has only one categorie, so you can use IN clause in the query:

@Query("SELECT m from Monitor m where m.categories IN :cats")

But if your Monitor.categories field is a list this won't be working. There should be more complicated logic.

Updated 11.08.2017: As I wrote in comments, you can transform list of category ids into the one string and compare with another. For this case the best way is create a view, but unfortunately this decision depends on SQL-engine. For PostgreSQL you can use something like this:

select m.*, string_agg(mc.catid, ',') as catids 
FROM monitor as m
LEFT JOIN messagecategories as mc on mc.monitor_id = m.id
GROUP BY m.id;