Hibernate version: 3.0.5

I want a HQL which finds all movies with specific categories.

There is a many-to-many mapping between a Movie and a MovieCategory

Movie:
...
<set name="movieCategorys" table="xod_movies_categorys" cascade="save-update" lazy="false">
<key column="movieId" not-null="true"/>
<many-to-many class="...MovieCategory" outer-join="auto"
column="movieCategoryId"/>
</set>
...

MovieCategory:
...
<set name="movies" table="xod_movies_categorys" cascade="save-update" lazy="true">
<key column="movieCategoryId" not-null="true"/>
<many-to-many class="...Movie" outer-join="auto" column="movieId"/>
</set>
...


My problem is how to make the join. The number of categories to find the movie with will be dynamic. It could be 1,2,3 or more.

An example with an native SQL, but not with a dynamic number of categories:

select a.*
from xod_movie a, xod_movie_basic_metadata b
where mediaType = 'MOVIE'
and movieId IN (
select mcs.movieId
from xod_movie_category mc, xod_movies_categorys mcs
where mcs.movieCategoryId = mc.movieCategoryId
and mc.name IN ('Action', 'Thriller')
GROUP by mcs.movieId
having count(mcs.movieId) = 2
)

can this be done with HQL and how?

Alternative I have tried with the Criteria API using an example object, but no luck here either.

Help would be appreciated.

Try the following criteria query:

hi peavish,



I have tried something similar to your above example, but with an group by projection on the criteria. The solution would work if it was possible to add a "having" clause to the criteria... I only need movies where ALL categories in the list is represented. With 3.0.5 there dosn't seem be a way to add a "having" clause.

The solution I ended up with is a dynamic build HQL where I insert all the relevant values and also using the "having" clause.

regards Claus

Hi claus,

i'm facing the same problem... Can you post your HQL statement?

thanks,
jan

of course:

StringBuffer hql = new StringBuffer();
hql.append("SELECT m FROM Movie m ");
hql.append("JOIN m.movieCategorys categories ");
hql.append("WHERE categories.name IN ");
hql.append("?,?,?....."); //Dynamic added depending on number of categories
hql.append("AND categories.operatorRef_movieContentOwner.movieContentOwner = :movieContentOwner ");
hql.append("GROUP BY m.id ");
hql.append("HAVING COUNT(m.id) = :sizeOfCategories");

Query query = getSession().createQuery(hql.toString());
//lets place the category names in the HQL:
int position = 0;
for (MovieCategory category : categories) {
query.setString(position, category.getName());
position++;
}
query.setLong("sizeOfCategories", categories.size());
query.setParameter("movieContentOwner", owner);
query.setFirstResult(offset);
query.setMaxResults(maxResults);
return query.list();

I would have been more pleased with a soultion with less string concat, but it was the only solution I could find...

regards Claus