Grails在SQL中计算字段 [英] Grails calculated field in SQL

问题描述

我有一个域名

I have a domain

class InvoiceLine {

String itemName
BigDecimal unitCost
Integer quantity
}

}

我想使用.withCriteria创建一个grails闭包，它会对（unitCost *数量）进行聚合，以便最终生成sql

I would like to come up with a grails closure using .withCriteria that does an aggregation of the (unitCost * quantity) so that I end up with sql

select item_name, sum(unit_cost * quantity) from invoice_line group by item_name;

现在，我可以做的最好的是

For now, the best I could come up with is

def result = InvoiceLine.withCriteria {

        projections {
            groupProperty('itemName')
            sum ('quantity * unitCost')
        }
    }

不幸的是，当我跑步时，上面的代码。任何人都有任何想法我可以如何实现我的目标？任何帮助都非常感谢。

Unfortunately, grails chokes up when I run the code above. Anyone have any idea how I could achieve my objective? Any help is highly appreciated.

推荐答案

它是否需要成为标准查询？ HQL在这里工作得很好：

Does it need to be a criteria query? HQL works great here:

def result = InvoiceLine.executeQuery(
  "select itemName, sum(unitCost * quantity) " + 
  "from InvoiceLine " +
  "group by itemName")

结果将会是一个 List Object [] 其中1st元素是一个String（名称）和2nd是一个数字（总数），所以例如你可以迭代类似于



The results will be a List of Object[] where the 1st element is a String (the name) and the 2nd is a number (the sum), so for example you could iterate with something like

results.each { row ->
   println "Total for ${row[0]} is ${row[1]}"
}

这篇关于Grails在SQL中计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！