1

Using the standard NHibernate example of Cats and Kittens, how would I use ICriteria to sort Cats based on Kitten count? For example, I want to do something like:

ICriteria crit = Session.CreateCriteria(typeof(Cat));
return crit.Order(Order.Asc("**Kittens.Count**"));

Anyone know how to achieve this?

2 Answers2

1

In HQL you can do it like this:

select cat
from Eg.Cat cat
join cat.Kittens kitten
group by cat
order by count(kitten) asc
gcores
  • 12,376
  • 2
  • 49
  • 45
1

See http://forum.hibernate.org/viewtopic.php?p=2401219

It's Java Hibernate but almost the same thing (Projection, AddOrder)

Mauricio Scheffer
  • 98,863
  • 23
  • 192
  • 275
  • When I do projection & AddOrder crit.SetProjection(Projections.Alias(Projections.Count("Kittens"), "kcnt")).SetResultTransformer(CriteriaUtil.DistinctRootEntity).List(); The list has only 1 result -- an integer! – noah.blumenthal Mar 03 '09 at 01:35
  • If you want the full entity plus a count(*), it's not easily doable... see http://opensource.atlassian.com/projects/hibernate/browse/HHH-3537 – Mauricio Scheffer Mar 03 '09 at 12:15