NHibernate 2.1: LEFT JOIN on SubQuery with Alias (ICriteria)

Question

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

SomeTable 1:n AnotherTable

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

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

Using multiple GROUP BYs is not efficient as well.

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

Answer 1

Unfortunately, Criteria is a bit restricted.

Try this:

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

Generally:

• 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.