CODEWITHSUNDEEP
nhibernatecriteria
Peter R
Peter R

Reputation: 21

Nhibernate Criteria Group By clause and select other data that is not grouped

I have a parent child relationship, let's say class and children. Each child belongs to a class and has a grade. I need to select the children (or the ids of the children) with the lowest grade per class.

session.CreateCriteria(typeof(Classs))
 .CreateAlias("Children", "children")
 .SetProjection(Projections.ProjectionList()
     .Add(Projections.Min("children.Grade"))
     .Add(Projections.GroupProperty("Id"))
 )
 .List<Object[]>();

This query returns me the lowest grade per class, but I don't know which child got the grade. When I add the children's Id to the group, the group is wrong and every child gets returned.

I was hoping we could just get the id's of those childs without grouping them. If this is not possible, then maybe there is a way to solve this with subqueries?

Upvotes: 2

Views: 4845

Answers (1)

Firo
Firo

Reputation: 30813

if min grade is unique:

select (select id from child c where c.classs_id = classs.id and c.grade = Min(classs.grade)) as child_id, classs.id from classs join child group by classs.id

transalted to Criteria

subquery = detachedCriteria.For<Child>("this")
  .Add(Restrictions.EqProperty("this.Classs", "classs.Id"))
  .Add(Restrictions.EqProperty("this.Grade", Projections.Min("classs.Grade")))
  .SetProjection(Projections.Id())


session.CreateCriteria(typeof(Classs), "classs")
  .CreateAlias("Children", "children")
  .SetProjection(Projections.ProjectionList()
     .Add(Projections.Alias(Projections.SubQuery(subquery)), "child_id")
     .Add(Projections.GroupProperty("Id"))
  )
  .List<object[]>()     // contains child_id and id (classs)

i cant test it right now, but you should get the idea

Upvotes: 1

Related Questions