HQL, query collection, with collection

Erik Forsberg · **Joined:** Wed Sep 19, 2007 2:59 am **Posts:** 14

Hi!
Im trying to write a search-function and i have the following problem, dont know how to best solv it in hql.

I have the objects Education and Category

Education
Id :GUID
Name :String
Categories: IList<Category>

Category
Id :GUID
Name:String

Calling my search function i pass a List<Category> in-parameter, and i want to get all the Education objects matching at least all the Categories in the List<Category> in-parameter.

Whats the best way to solve it in HQL?

In SQL i could use Intersect in a where subquery. I cant use in here because i want it to match all the items in the inparam, not one of them alone.

Thanks!

michal · **Posted:** Thu Jan 31, 2008 5:45 am

My guess :

Pass two parameters, :cats (categories list) and :n ( :cats.Count )

Code:

select edu from Education edu where (select count(c) from edu.Categories c where c in (:cats) ) = :n