Case Statements in ARel

Question

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.

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

Pompea

Answer 1

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

Insurrectionary