How to join subclass of the association class

dhamza · **Joined:** Thu Nov 20, 2008 11:42 am **Posts:** 4

Hi there,
I have Cat, Dog, Tiger entities, and Animal entity as base class. Mapping to this is "table per subclass". Also, Person class has many Animals, using "bag" xml-element (bidirectional relation).

Each Cat and Tiger has property as ClawSize. But this property (assuming it is not significant to Dog nor Animal) will NOT belong to Animal nor Dog.

I want to fetch all people (Persons :) ) that conform at lease one of these criteria:

Any cat having ClawSize > 10 units, or any tiger having ClawSize > 15 units.

All I want to query it at one round trip, i.e. to generate one sql.

And also I want to prefetch (eager load), all animals of each person, i.e. not only those matching the criteria (but it's not that important).

Can someone provide any help, please. Using ICriteria would be much better, but I couldn't do by it. So I tried with HQL, and no success. In native sql, I can, but I dont think it would be best practice using this in NHibernate (or Hibernate).

NHibernate build version: 1.2.1.4000.

dhamza · **Joined:** Thu Nov 20, 2008 11:42 am **Posts:** 4

No answer, too hard question for you :) ?

wolli · **Posted:** Mon Nov 24, 2008 4:06 am

What have you tried so far and what was the result (exception, wrong resultset, ...) ?

I would try something like this:

Code:

from Person p join fetch p.Animals a 
where (a.class = Cat and a.ClawSize > 10) or 
   (a.class = Tiger and a.ClawSize > 15)

dhamza · **Joined:** Thu Nov 20, 2008 11:42 am **Posts:** 4

wolli,
your suggestion didn't help. It generates different sql that we expect. It generates something like:

Code:

select ...
from
  ...
  inner join Animal a0
  left outer join Cat a1 on a1.ID = a0.ID
  left outer join Tiger a2 on a2.ID = a0.ID
  left outer join Dog a3 on a3.ID = a0.ID
  ...
where
  ((case when a1.ID is not null then 1 .. when a3.ID is not null then 3 end = 1) and a1.ClawSize > 10)
  or
  ((case when a1.ID is not null then 1 .. when a3.ID is not null then 3 end = 2) and a1.ClawSize > 15)

as you see, in both clause a1 (Cat) alias is used. Since a0 doesnt have ClawSize property, nhibernate finds first match, and uses it all the way down.