Projections + join with HQL query

gistfile1.groovy

/* Why HQL?

Cause:

* Criteria + projections + join = "joins are ignored" 
* Criteria + projections + forced join = "joins not ignored but joined tables are fetched lazy"
* HQL do not need explicit join clauses; joins are inferred automatically for one-to-one & many-to-one associations
* GORM criteria is buggy 

*/

def result = Order.executeQuery('select o.id, o.total, o.status from Order as o where o.total = :total order by o.status desc, o.total desc', [total:200.0], [max: 2, offset: 0])
def totalCount = Order.executeQuery('select count(o.id) from Order as o where o.total > :total', [total:200.0])
println "Result Class: ${result.class}"

println "Result: ${result}"

println "Result size: ${result.size()}"

println "Result total: ${totalCount[0]}" // Returns Long