How to match all children using hql

Question

What is the best / preferred way to select a (separate) list of parent objects that has a child collection containing matches for ALL entries in the child parameter list?

I execute the "any" version of my search as follows:

select p.Id, p.Name from parent p 
where exists(from p.Children c where c in (:childList))

However, I am a little puzzled by how best to perform the “all” version of this search. I am currently creating hql "on the fly" for every child that interests me in comparing; something like that:

select p.Id, p.Name from parent p 
where :child1 in elements(p.Children)
and   :child2 in elements(p.Children)
-- etc...

I cannot help but think that there is a better way to do this; can someone point me in the right direction?

For reference, I am using NHibernate 2.1.2

Answer 1

, . " (p.Children) (: childList)" SQL. ...

var query = session.CreateQuery("select p from Parent p join p.Children c where c in (:childList) group by p.Id, p.Name having count(p) = :childListSize");
var children = new[] {session.Load<Child>(1),session.Load<Child>(2),session.Load<Child>(3)};
query.SetParameterList("childList", children);
query.SetParameter("childListSize", children.Length);

HQL...

select p from Parent p 
join p.Children c 
where c in (:childList) 
group by p.Id, p.Name 
having count(p) = :childListSize

, , , , , . !

, . NH Id .

BTW - NH3, - , NH2.1.2.

Answer 2

, , - ...

select distinct c.Parent.Id, c.Parent.Name from Child c 
where c in (:childList))