This question is a copy from:
http://stackoverflow.com/questions/6925587/hql-join-problem
I would appreciate an answer please. Thanks!
This is the question:
I have no problem writing sql for the following problem. However, I
would like to write it as HQL or ICriteria. I am using fluent
nhibernate and the latest nhibernate. The situation:
There are 6 classes A B C D AC AD. B inherits from A. AC represents a
m:m relationship between A and C and AD represents a m:m relationship
between A and D. Let us assume that all classes have an ID column. I
would like to count the number of Cs and Ds B is associated with.
There is no IList of, for example, Cs in A (and B for that matter).
Still the classes are associated ...
Here is some code (simplified):
public class A : Entity
{
}
public class B : A
{
}
public class C : Entity
{
}
public class D : Entity
{
}
public class AC : Entity
{
public virtual A A { get; set; }
public virtual C C { get; set; }
}
public class AD : Entity
{
public virtual A A { get; set; }
public virtual D D { get; set; }
}
Is this possible to use HQL and ‘left join’ (to also show the Bs that
have zero Cs and Ds) in my particular case?
Thanks.
Christian
PS:
I have played a bit with theta style joins but did not get the
expected results and I don’t think ‘left joins’ are possible here
aren’t they?
PPS:
This theta-style join kind of works but only if B is assciated with at
least 1 C and D:
select
B.Id,
count(distinct AC.C.Id),
count(distinct AD.D.Id)
from AC AC, AD AD, B B
where AC.A.Id = B.Id and AD.A.Id = B.Id
group by B.Id
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
