HQL连接问题

发布于 2024-11-28 04:34:46 字数 1017 浏览 1 评论 0原文

对于以下问题,我编写sql没有问题。但是,我想将其写为 HQL 或 ICriteria。我正在使用 Fluent nhibernate 和最新的 nhibernate。情况:

ABCD AC AD 有 6 个班级。 B继承自A。AC代表A和C之间的am:m关系,AD代表A和D之间的am:m关系。让我们假设所有类都有一个ID列。我想计算与 B 相关的 C 和 D 的数量。

例如,A(和 B)中不存在 C 的 IList。这些类仍然是关联的...

这是一些代码(简化的):

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

在我的特定情况下,是否可以使用 HQL 和“左连接”(也显示具有零 C 和 D 的 B)?

谢谢。

Christian

PS:

我玩过一些 theta 风格的连接,但没有得到预期的结果,我不认为“左连接”在这里是可能的,不是吗?

PPS:

这种 theta 式连接类型有效,但前提是 B 与至少 1 个 C 和 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

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

老旧海报 2024-12-05 04:34:47

您可以使用两个查询并对结果求和:

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

我看不到用单个查询来实现的方法。您的类似乎不是为这种查询而设计的...(这可能表明设计不合适,但并不需要如此。这取决于如何通常此查询适用于您的应用程序。)


相反,如果您拥有这些导航路径,而不询问关系类:

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

您将通过以下方式获得列表的大小:

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

或者在内存中更容易:-)

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

完全删除关系类,它可能如下所示:

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

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; }
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文