NHibernate 2.1:使用别名(ICriteria)对子查询进行左连接 [英] NHibernate 2.1: LEFT JOIN on SubQuery with Alias (ICriteria)

问题描述

我基本上是在尝试使用 NHibernate ICriteria 接口创建此查询:

I am basically trying to create this query with NHibernate ICriteria interface:

SomeTable 1:n AnotherTable

SomeTable 1:n AnotherTable

SomeTable 有列:PrimaryKey、NonAggregateColumn
AnotherTable 有列:PrimaryKey、ForeignKey、AnotherNonAggregate、YetAnotherNonAggregate

SomeTable has columns: PrimaryKey, NonAggregateColumn
AnotherTable has columns: PrimaryKey, ForeignKey, AnotherNonAggregate, YetAnotherNonAggregate

SELECT 
      table1.NonAggregateColumn, 
      subquery.SubQueryAggregate1, 
      subquery.SubQueryAggregate2
FROM 
      SomeTable AS table1
      LEFT JOIN
      (
          SELECT 
                table2.ForeignKey,
                COUNT(table2.AnotherNonAggregate) AS SubQueryAggregate1,
                AVG(table2.YetAnotherNonAggregate) AS SubQueryAggregate2
          FROM AnotherTable AS table2
          GROUP BY (table2.ForeignKey)
      ) AS subquery ON subquery.ForeignKey = table1.PrimaryKey

很明显，使用投影子查询效率不高，因为 SQL 必须扫描表两次(每个聚合一个投影子查询).

It is clear that using Projection subquery is not very efficient, since SQL has to scan the table twice (one projection subquery per aggregate).

使用多个 GROUP BY 也效率不高.

Using multiple GROUP BYs is not efficient as well.

有解决办法吗?到目前为止，我一直在求助于使用原始 SQL，但这对于复杂的报告来说变得很笨拙.

Is there a solution for this ? So far I've been resorting to using raw SQL but this is getting unwieldy for complex reports.

推荐答案

不幸的是，标准有点受限.

Unfortunately, Criteria is a bit restricted.

试试这个:

session.CreateCriteria(typeof(SomeTable), "st")
  .SetProjection( Projections.ProjectionList()
    .Add(Projections.GroupProperty("st.id"))
    .Add(Projections.GroupProperty("st.NonAggregateColumn"))
    .Add(Projections.RowCount(), "rowcount")
    .Add(Projections.Avg("at.YetAnotherNonAggregate"), "avg"));
  .CreateCriteria( "st.OtherTables", "at", JoinType.InnerJoin)
  .List<object[]>();

您可能需要多玩一会，这更像是一种猜测.这种方式也可能是不可能的.

You probably need to play around a bit, it's more of a guess. It also might be impossible this way.

它应该产生这样的结果:

It should produce something like this:

select
  st.id,
  st.NonAggregateColumn,
  count() as "rowcount",
  avg(at.YetAnotherNonAggregate) as "avg"
from
  SomeTable st inner join AnotherTable at on ...
group by
  st.id,
  st.NonAggregateColumn

一般:

• 您可以使用 DetachedCriteria 进行子查询.见 文档 了解更多详情.
• 您不能在 where 子句中使用 Criteria 和 filter 制作笛卡尔积.(这只适用于 HQL).
• 不能将子查询添加到 from 子句中(因为这会导致笛卡尔积).您只能将它们放在 where 子句中(in、exists 等)
• 您可能可以从 AnotherTable 开始，然后导航到 SomeTable.这可能是另一种解决方案.

• You can make subqueries using DetachedCriteria. See the docs for more details.
• You can't make a cartesian product with Criteria and filter in the where clause. (This only works with HQL).
• Subqueries can not be added to the from clause (because that would result in a cartesian product). You can only put them to the where clause (in, exists etc.)
• You could probably start with AnotherTable and navigate to SomeTable. This might be a alternative solution.

这篇关于NHibernate 2.1:使用别名(ICriteria)对子查询进行左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！