Access collection field in Spring Repository with custom Query

Question

I'm trying to make a query to search all Posts from my application containing a keyword on the content or on the tags name but only those who are public. I'm trying to make the JPQL search but I don't know how to access the tags.name property.

Note: Post is an entity which has a List of Tag entity;

I have tried this but it's not working (as I expected):

@Query("SELECT p FROM Post p WHERE (p.content LIKE CONCAT('%', LOWER(:keyword),'%' OR p.tags.name LIKE CONCAT('%', LOWER(:keyword)) AND (p.open IS TRUE)")

I have looked at the documentation but I don't see any option to manage this, what is the best way to go here?

Thanks!

Answer 1

Since multiple tags can be associated with a post the relationship is a @OneToMany from Post to Tag. A join should work in this scenario.

Try this.

@Query("SELECT p FROM Post p left join p.tags pTags WHERE (p.content LIKE CONCAT('%',LOWER(:keyword),'%' OR pTags.name LIKE CONCAT('%',LOWER(:keyword)) AND (p.open IS TRUE)")

PS : I have not tested this, but it should work.