How can I express joining a grouped subquery using NHibernate?

Question

I am trying to express a SQL query using the NHibernate APIs and I am having difficulty because I think it is database oriented while NHibernate is object oriented.

SQL (works fine):

select outerT.id, outerT.col1, outerT.col2, outerT.col3 from tbl outerT inner join (select max(innerT.id) from tbl innerT group by innerT.col1) grpT on outerT.id = grpT.id 

Essentially, this is a table self-join with a subset. I suppose I could try turning self-connectivity into a constraint:

 select outerT.id, outerT.col1, outerT.col2, outerT.col3 from tbl outerT where outerT.id in (select max(innerT.id) from tbl innerT group by innerT.col1) 

But I'm not sure how to express this with NHibernate; I am fighting the DetachedCriteria ProjectionList and want to select only max(id) when grouping col1 .

Thanks so much for your suggestions!

Answer 1

I don’t know if I should post this as a new answer or add it as a comment to the original question, but I think I have solved a similar problem in this thread:

Subquery selection in NHibernate with the Critieria API

Answer 2

AFAIK, you cannot join subqueries at all in NHibernate, but you can re-arrange the query to use either EXISTS or IN to replicate the same functions.

I understand that this question requires that this be done using the criteria API, but I thought I would post a version of HQL that could give someone else some ideas.

 var results = session.CreateQuery("from Product p where p.Id in ( select max(p2.id) from Product p2 group by p2.col1 )") 

I also found this JIRA problem related to the Criteria API, and not including the column group in select. Currently, it seems that you cannot achieve criteria using the API at all.

Group by property without adding select clause

UPDATE Using the example from the Monkey Coders post, you can do this:

 var subquery = DetachedCriteria.For<Product>("p") .SetProjection(Projections.ProjectionList() .Add(Projections.GroupProperty("p.Col1")) .Add(Restrictions.EqProperty("p2.Id", Projections.Max("p.Id")); var query = DetachedCriteria.For<Product>("p2") .Add(Subqueries.Exists(subquery)); 

What will create the following SQL

 select * from Product p2 where exists ( select p.col1 from Product p group by p.col1 having p2.Id=max(p.Id) )