How can I perform sorting ORDER BY expression in NHibernate

Question

select p.Id, sum(inc.Quantity) 
from Products p join Incomes inc
group by p.Id 
order by sum(inc.Quantity) >0

This query gives NHibernate.Hql.Ast.ANTLR.QuerySyntaxException on ORDER BY clause. I wonder is it possible to overcome this error and perform sorting by some expression?

UPDATE
I need to sort products list by name, price etc. BUT products with quantity == 0 must go to the bottom of the list (no matter what name, price it has). Also I need to perform paging of that list.

Answer 1

It's a while since I've done any NHibernate, but I'd have expected you to at least want to remove the > 0 bit of the ordering:

order by sum(inc.Quantity)

or possibly use an alias:

select p.Id, sum(inc.Quantity) totalQuantity
from Products p join Incomes inc
group by p.Id 
order by totalQuantity

I don't know if that'll work, but it's worth a shot :)

Answer 2

Try

select p.Id, sum(inc.Quantity) 
from Products p join Incomes inc
group by p.Id 
having sum(inc.Quantity) > 0
order by sum(inc.Quantity)

Order by is used for sorting. To filter on aggregated fields use having.

See http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-grouping

Answer 3

You need a syntax that is supported by SQL too. This works:

select p.Id, sum(inc.Quantity) 
from Products p join p.Incomes inc
group by p.Id 
order by case when sum(inc.Quantity) > 0 then 0 else 1 end

Nitpicking: why do your entities have plural names?