使用executeQuery（）对算术表达式进行Grails投影？ [英] Grails projection on arithmetic expression with executeQuery()?

问题描述

我需要获得每个商店每个订单所售出的所有商品的总和。我使用executeQuery（）在表达式上运行sum（）。它可以正常工作，如下所示，但我想知道是否有更好的方法可以做到这一点。

  StoreService {
 static transactional = false 
 
 def getTotalOrders（def store）{
 return Store.executeQuery（select sum（a.soldQuantity * a.soldPrice）as total b $ b from OrderItem一个内部联接a.order b内部联接b.store c 
其中c =：store，[store：store]）。get（0）
} 
} 
 
 store {
暂存storeService 
 
 def getTotalSales（）{
 storeService.getTotalSales（）
} 
 
 static hasMany = [商品：商品] 
 //无有许多订单
} 
 
商品{
 static belongsTo = [store：商店] 
 // no hasMany to OrderItem 
} 
 
 
订单{
 static hasMany = [orderItems：OrderItem] 
 static belongsTo = [store：Store] 
} 
 
 
 OrderItem {
 
 BigDecimal soldPrice 
整数soldQuantity 
 
 stati c belongsTo = [order：Order，item：Item] 
 
} 
  

我认为withCriteria（）会更容易阅读，但我无法弄清楚如何使用sum（）中的表达式来做到这一点，不会出于明显的原因。

 预测{
 sum（soldPrice * soldQuantity）
} 
  

谢谢

解决方案

您可以使用两种选项。

选项1

您可以添加

  OrderItem {
 BigDecimal soldPrice 
整数soldQuantity 
 BigDecimal totalPrice 
 
 static mapping = {
 totalPrice formula：sold_price * sold_quantity
} 
 
 static belongsTo = [order：Order， item：Item] 
 
} 
  

现在您的标准查询可以包含

 预测{
 sum（totalPrice）
} 
  

不仅如此，您可以使用动态查找器查询它 OrderItem.findAllByTotalPriceGreaterThan（20.00） as以及简单访问 printlnfin价格是$ {orderInstance.totalPrice} 。我们发现这真的很棒，但有时候你想在OrderItem被保存之前得到totalPrice，所以我们通常写一个简单的（不干的）getter

  BigDecimal getTotalPrice（）{
 totalPrice？：（soldPrice&&soldQuantity）？ soldPrice * soldQuantity：null 
} 
  

但是，如果您需要，您只需要这样的事情

$ b

选项2

在公式映射之前，我们用于下载到Hibernate Criteria API并使用sqlprojection投影作为我们的条件查询的一部分。

 预测{
作为字符串[]，
 [Hibernate.LONG]，作为类型[]的addProjectionToList（作为totalPrice的
sum（sold_price * sold_quantity），
 [totalPrice]） 
），sumProjection）
} 
  

我认为重要的是要注意，在公式和sql投影中，使用数据库中的列名和数据库特定的sum语法。

p>

I need to get a sum of all items sold per order per store. I am running a sum() on expression using executeQuery(). It works fine as shown below but I wanted to know if there is a better, groovier way to do it.

StoreService {
  static transactional = false

  def getTotalOrders(def store) {
    return Store.executeQuery("select sum(a.soldQuantity * a.soldPrice) as total 
             from OrderItem a inner join a.order b inner join b.store c 
             where c= :store", [store: store]).get(0)
  }
}

Store {
  transient storeService

  def getTotalSales() {
    storeService.getTotalSales()
  }

  static hasMany = [items: Item]
  // no hasMany to Order
}

Item {
  static belongsTo = [store: Store]
  // no hasMany to OrderItem
}


Order {
  static hasMany = [orderItems: OrderItem]
  static belongsTo = [store: Store]
}


OrderItem {

  BigDecimal soldPrice
  Integer soldQuantity

  static belongsTo = [order: Order, item: Item]

}

I think withCriteria() would be easier to read but I couldn't figure out how to do it with expressions within sum() wouldn't take for obvious reasons.

projections {
  sum("soldPrice * soldQuantity")
}

Thanks

解决方案

There are two options you can go with.

Option 1

You can add a formula mapping to your domain class then query it directly.

OrderItem {
  BigDecimal soldPrice
  Integer soldQuantity
  BigDecimal totalPrice

  static mapping = {
    totalPrice formula: "sold_price * sold_quantity"
  }

  static belongsTo = [order: Order, item: Item]

}

Now your criteria query can just contain

projections {
    sum("totalPrice")
}

Not only that but you can query it with dynamic finders OrderItem.findAllByTotalPriceGreaterThan(20.00) as well as simple access println "The final price is ${orderInstance.totalPrice}. We find this really nifty however there are times when you would want to get totalPrice before the OrderItem has been persisted so we usually write a simple(Not DRY) getter

BigDecimal getTotalPrice() {
    totalPrice ?: (soldPrice && soldQuantity) ? soldPrice * soldQuantity : null
}

But you only need this sort of thing if you require totalPrice before it has been persisted.

Option 2

Before formula mappings we used to drop down to the Hibernate Criteria API and use a sqlProjection Projection as part of our criteria query.

projections {
    addProjectionToList(Projections.sqlProjection(
            "sum(sold_price * sold_quantity) as totalPrice",
            ["totalPrice"] as String[], 
            [Hibernate.LONG] as Type[],
        ), "sumProjection")
 }

Note

I think it is important to note that in both the formula and the sql projection, use the column names in the database and your database specific sum syntax.

这篇关于使用executeQuery（）对算术表达式进行Grails投影？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！