javahibernatehqlin-clause

hql in-clause with multiple hits in child collection

I have a table/entity called Recipe with a child collection of type Tag. I want to be able to find a Recipe searching by two or more tags. Something like:

SELECT re FROM Recipe re JOIN re.tags t WHERE t in :tagsIds

but I only want those hits where the Tag collection contains all tagIds. Is it possible in HQL/SQL? (Maybe using Criteria?)

Thanks in advance.

Solution

  • Ok, so this did it. Thanks for the replies.

    String hql = "select r from Recipe r " +
                "join r.tags t " +
                "where t.id in (:tags) " +
                "group by r " +
                "having count(t)=:tag_count";
Query query = session.createQuery(hql);
query.setParameterList("tags", tagIds);
query.setInteger("tag_count", tagIds.size());
List<Recipe> recipes = query.list();