HQL Question - not exists with many to many

[Tim Haines]

Hi there,

I have a many to many reln between A and B.  A has a collection called Bs, and B has a collection called As.  On the "Manage B" page I want to create a list of all A's that haven't been assigned to B yet.  In SQL this would be something like "select * from A where not exists (select * from AB where AID = A.ID and BID = 'MyIDToExclude')"  How do I do this in HQL?

Either the NHibernate doc is a little fuzzy in this area, or the time of night is making me a little fuzzy.

Hmm - actually, may have just solved this with:

from A where not exists (from A.Bs as B where B.ID = 'MyIDToExclude')

Will need to test it (and get around to installing NHQA) in the morning when I'm more awake.

Cheers,

Tim.

[Ayende Rahien]

Something like:

from A a where a not in (select b.As from B b)

[Tim Haines]

Almost.  Your NHibernate Query Analyzer helped me find that what I needed was:

from A a where a not in (select elements(b.As) from B b)

[josh robb]

> Almost. Your NHibernate Query Analyzer helped me find that what I needed
> was:
>
> from A a where a not in (select elements(b.As) from B b)

Yep - My model is _full_ of many to manys and I had to discover this
dark little corner of NH by myself. It seems strangely undocumented. I
remember I couldn't even find a description of what elements did in
the docs back then. (May have changed - 18 months is a long time).

j.

[Ayende Rahien]

It is there, along with other delightful things such as indexes, max_index, etc .
Indexes() can be useful if you want to return just the keys from a dictionary of entities to entities (scary schema)

[josh robb]

> It is there, along with other delightful things such as indexes, max_index,
> etc .
> Indexes() can be useful if you want to return just the keys from a
> dictionary of entities to entities (scary schema)

Thats exactly what I ment - I can't find any docs at all for max_index
and the although elements() is demonstrated here - it's hardly
explained..

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/queryhql.html#queryhql-select

If I felt I understood it well enough - I'd submit a documentation patch.

j.

[Ayende Rahien]

Sorry, that is maxIndex, minIndex.
Those are basically aggregate functions over collections, so something like:

select maxIndex(emp.Salaries) from Employee emp

will be translated to:

select max(date) from salaryPayments inner join emp ...

Assuming that salaries is IDictionary<DateTime, Salary>

On 5/29/07, josh robb <josh...@fastmail.fm > wrote:

[Tim Haines]

That's the only documentation I've seen of elements() too.  Luckily it's used in the example of the in subselect later too, which was what made me take a look:

select mother from 
Eg.Cat as mother, Eg.Cat as kit
where kit in elements(mother.Kittens)

Tim.