ARel中的案例陈述

本文介绍了ARel中的案例陈述的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用ARel清理用MySQL案例语句编写的ActiveRecord代码.我知道Arel可以处理案例陈述，但不确定如何使用它.

I'm trying to clean up an ActiveRecord code written with MySQL case statements using ARel. I know Arel is able to handle case statements, but not sure how to use it.

所以我有一个带有数量和user_id(外键)列的订单表.我正在动态计算订单价格，因为基于订购数量存在不同的定价方案.这是AR代码的样子:

So I have an orders table with quantity and user_id(foreign key) columns. I'm dynamically computing the price of orders as there are different pricing schemes based on the quantity ordered. This is what the AR code looks like:

def orders_with_price <<-SQL orders.*, SUM(CASE orders.quantity WHEN 2 THEN 500 * orders.quantity WHEN 5 THEN 450 * orders.quantity WHEN 10 THEN 350 * orders.quantity END) AS total_price SQL end Order. select(orders_with_price). group("orders.user_id"). having("total_price > ?", minimum_price). order("total_price")

推荐答案

我认为从Arel v7.x引入 Arel :: Nodes :: Case (可用于case语句).您可以将查询改写为:

I think from Arel v7.x introduced Arel::Nodes::Case which can be used for case statements. You can re-write this query as:

def case_statements orders = Order.arel_table Arel::Nodes::Case. new(orders[:quantity]). when(2).then(orders[:quantity] * 500). when(5).then(orders[:quantity] * 450). when(10).then(orders[:quantity] * 350) end orders = Order.arel_table Order. select(Arel.star). select(Arel::Nodes::Sum.new(case_statements).as("total_price")). group(orders[:user_id]). having("total_price > ?", minimum_price). order("total_price")