Grails projection on arithmetic expression with executeQuery()?

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.

As of Grails 2.2, SQL projections are supported without having to drop down to the Hibernate Criteria API. Note that a formula mapping may still be more desirable, but with this you can directly implement the sum('soldPrice * soldQuantity') style projection as per your question.

http://grails.org/doc/latest/guide/single.html#criteria

I'd try to add a transient derived property total to OrderItem and use sum() on it.

Try SQL Projection

projections {
  sqlProjection 'sum("soldPrice * soldQuantity") as total', 'total', StandardBasicTypes.DOUBLE
}

For farther details http://docs.grails.org/2.5.6/guide/GORM.html#criteria