Hibernate Community • View topic - Group By in Criteria query

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

Group By in Criteria query

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

tindbaek

Post subject: Group By in Criteria query

Posted: Mon Jun 11, 2007 10:13 am

Newbie

Joined: Sat Mar 10, 2007 12:46 pm
Posts: 5

I want the latest exports successful export for each of several given export groups.

The SQL is like this:

Select *
From ExportFile res
Inner join
(
Select max(ExportFileID)
From ExportFile ex
Where ex.Status = 1
Group By ExportFileGroupID
) latestFiles ON latestFiles.ExportFileId = res.ExportFileId
Where res.ExportFileGroupID in (@groupsToGet)
(sql has not been syntax checked)

We want to write this as a Criteria query, and we have tried a lot of different combinations with no luck.

Can anyone point us in the right direction?

Top

sunraider

Post subject:

Posted: Mon Jun 11, 2007 12:38 pm

Newbie

Joined: Mon Jun 11, 2007 6:08 am
Posts: 3

Hi tindbaek,

NHibernate reference document under section 12.6 Projections, aggregation, grouping and 12.7 Detached Queries and Subqueries may be the area to look for. The combination of 12.6 & 12.7 should work for you.

Extract from the reference document page 110:

Code:

DetachedCriteria avgWeightForSex = DetachedCriteria.For(typeof(Cat), "cat2")
.SetProjection( Projections.Avg("Weight") )
.Add( Expression.EqProperty("cat2.Sex", "cat.Sex") );
session.CreateCriteria(typeof(Cat), "cat")
.Add( Subqueries.Gt("weight", avgWeightForSex) )
.List();

I don't know if this is correct but can be something on these lines: