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

问题描述

我需要获取每个商店每个订单销售的所有商品的总和.我正在使用 executeQuery() 对表达式运行 sum().它运行良好，如下所示，但我想知道是否有更好、更好的方法来做到这一点.

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]

}

我认为 withCriteria() 会更容易阅读，但我无法弄清楚如何使用 sum() 中的表达式来执行它，原因很明显.

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")
}

谢谢

推荐答案

您可以选择两个选项.

选项 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")
}

不仅如此，您还可以使用动态查找器查询它 OrderItem.findAllByTotalPriceGreaterThan(20.00) 以及简单的访问 println "最终价格是 ${orderInstance.totalPrice}. 我们发现这真的很不错，但是有时您希望在 OrderItem 被持久化之前获得 totalPrice，因此我们通常编写一个简单的(非 DRY)getter

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
}

但只有在持久化之前需要 totalPrice 时才需要这种东西.

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

选项 2

在公式映射之前，我们曾经下拉到 Hibernate Criteria API 并使用 sqlProjection Projection 作为我们的标准查询的一部分.

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")
 }

注意

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

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屋！