HQL join problem

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

can you please provide the classes as code? It would be much easier to understand which properties are available. — Stefan Steinegger
done. see class code. is this ok? please note that things are simplified. — cs0815
Why have you got classes to represent the M:M relationship? You're not associating any extra data, so it seems frivolousness and confusing — kͩeͣmͮpͥ ͩ
I don't see anything wrong with using two one-to-many relationships to model a many-to-many one. This gives you more flexibility in the long run especially when the many-to-many has additional columns ... — cs0815
These aren't one-to-many relations, these are many-to-one relations. OO relations aren't bidirectional as DB relations are. You don't have any list in your object model, nor any back relations. This makes it so hard to navigate. — Stefan Steinegger

Stefan Steinegger Stefan Steinegger · Accepted Answer · 2011-08-03T13:45:07

You may use two queries and sum the result:

int numberOfLinks;

numberOfLinks = session
  .CreateQuery(
    @"select count(*)
    from AC ac
    where ac.A = :b"
  .SetEntity("b", myB)
  .UniqueResult<int>();

numberOfLinks += session
  .CreateQuery(
    @"select count(*)
    from AD ad
    where ad.A = :b"
  .SetEntity("b", myB);

I can't see a way to make it with a single query. Your classes don't seem to be designed for that kind of query... (which may be a sign that the design isn't appropriate, but doesn't need to be. It depends of how typical this query is for your application.)

In contrast, if you had these navigation paths, without questioning the relation classes:

public class A : Entity
{
  IList<AC> ACs { get; private set; }
  IList<AD> ADs { get; private set; }
}

you would get the size of the lists by:

numberOfLinks = session.CreateQuery(
  @"select size(ACs) + size(ADs)
  from B
  where ...")

or even easier in memory :-)

numberOfLinks = myB.ADs.Count + myB.ACs.Count;

Completely removing the relation classes, it could look like this:

public class A : Entity
{
  IList<C> Cs { get; private set; }
  IList<D> Ds { get; private set; }
}

public class C : Entity
{
  IList<A> As { get; private set; }
}

public class D : Entity
{
  IList<A> As { get; private set; }
}

HQL join problem

1 Answers