NHibernate 2.1: LEFT JOIN on SubQuery with an alias (ICriteria)

Question

I am basically trying to create this query using the 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 the Projection subquery is not very effective, because SQL has to double-scan the table (one projection subquery per population).

Using multiple GROUP BYs is also inefficient.

Is there a solution for this? So far I have resorted to using raw SQL, but this is becoming cumbersome for complex reports.

Answer 1

Unfortunately, the criteria are a bit limited.

Try the following:

 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 a little, this is more of a hunch. It may also 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 create subqueries using DetachedCriteria . See documents for more details.
• You cannot make a Cartesian product with criteria and filter in the where section. (This only works with HQL).
• Subqueries cannot be added to the from clause (because this will result in a Cartesian product). You can only put them in a where clause ( in , exists , etc.).
• Perhaps you can start with AnotherTable and go to SomeTable . This may be an alternative solution.