CODEWITHSUNDEEP
hibernatenhibernatefluent-nhibernate
cs0815
cs0815

Reputation: 17388

HQL join problem

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

Upvotes: 0

Views: 369

Answers (1)

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

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

Upvotes: 1

Related Questions