sql - 将包含子查询的 SQL 查询转换为 grails 中的 GORM 查询
问题描述
我有以下在 grails 版本 2.5.5 上使用的域名
class OrderBook {
int orderNumber
String mutationCode
Date mutationDate
User orderUser
OrderStatus status
OrderVendor vendor
String catalogueNumber
String orderDescription// (catalogue name) auto-completion from a vendor
Float pricePerUnit
Integer numberOfUnit
float vat
String orderUnit
Float shipHandleCost
Budget budget
boolean reception
String purchaseOrder
String comment
static constraints = {
orderNumber blank: false, unique: false, display:false
mutationCode blank: false, inList: ["c", "u", "d"], display:false //c: created, u:updated, d:deleted
mutationDate blank: false, display: false
orderUser blank: false, unique: false
status blank: false, unique: false
vendor blank: false, unique: false
catalogueNumber blank: false, unique: false
orderDescription blank: false, unique: false
pricePerUnit blank: false, unique: false
vat blank: false, unique: false
orderUnit blank: true, nullable: true
shipHandleCost blank: false, unique: false
budget blank: false, unique: false
purchaseOrder blank: false, unique: false
comment blank: true, nullable: true, type: 'text'
}
}
我编写了以下 SQL 请求,使用它可以正常工作executeQuery
:
def orderBookLastList = be.vib.emone.OrderBook.executeQuery('''from OrderBook o where o.mutationDate in (select max(mutationDate) from OrderBook b where o.orderNumber=b.orderNumber) AND NOT o.mutationCode=:mutationDelete order by id''', [mutationDelete:'d'], [max: params.max, offset: params.offset])
我想用 GORM 风格写它,但我不知道如何处理子查询。我尝试使用 WHERE,但我的试验都没有奏效。
欢迎任何提示