Select an entity according to a list of associated entities

Question

I have the following database schema :

Two tables, books and tags, with n-m relationship.

Books - Tags

We can have for example the book 1, with tags {A,B,C}, and book 2, with tags {A}.

I would like to select the books according to a list of tags.

For example : selected tags list : {A,B} -> book 1

My idea was to use the MINUS SQL function, to subtract book tags list to the selected tags list, and return the book if the list was empty. Unfortunately, this SQL function is not supported by HQL.

Any idea about that ?

Answer 1

1/ i would try another approach, something like that :

from
  book 
where
  book.id in (
  select
    b.id
  from
    book b
  where
    b.tags.name in (?)
  group by
    b.id
  having
    count(b.tags.id) = ?
)

• parameters:
  • first ? : tag list
  • second ? : tag list size
• I think all the functions used in my hql are supported.
• If you don't need the whole object, but only one property of book like the id, you can remove the outer select

2/ if you prefer your query, you can still use a sql-query (see http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html section 16.2).