How to match all children using hql

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

I'm performing the "any" version of my search like this:

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

However, I'm a little stumped on how best to p开发者_开发问答erform the "all" version of this search. Currently I'm creating hql on the fly for each child that I'm interesting in matching; something like this:

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

I can't help but think there's a better way to do this; can anyone point me in the right direction?

For reference, I'm using NHibernate 2.1.2

Interesting question and I didn't find an easy answer. Simple attempts using "all elements(p.Children) in (:childList)" failed to produce valid SQL. This monster ended up working...

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);

Let's break out the HQL...

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

We are creating an inner join with children, which produces multiple rows, selecting those rows that where the child is in the childList, grouping by the parent, and seeing if we got the expected number of rows and only returning those parents. Whew!

Note that you have to explicitly specify all properties in the group by. Otherwise NH only includes the Id and the group by fails.

BTW - I ran this against NH3, though I can't think of any reason it wouldn't work with NH2.1.2.

you could query the child and select the parent, as long as the association exists, something like this...

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

Just using some of logic we can find the answer, First of all, you had the answer in this query:

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

The problem here is that here we search about parent having at least a child existing in the "childList" list, but we need a parent that all their children are in the childList.. but also we know that the parent which doesn't have at least children not existing in childList is the same thing. So:

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

Optionally, we have the parent without children is another problem sometimes according to the situation, So:

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