Advanced ActiveRecord Query: Filter Sum through Nested Associations

Question

Imagine I am an eCommerce site with the following database structure:

Orders has_many OrderItems
OrderItem belongs_to Product
The order_items table has a column, quantity.
The product table has a column, price.

I want to make a scope in my Order model that is able to filter the Orders by their total price. That is the sum of the Order's (order_item quantities * the order_item's product price).

Intended Use:

#Get orders that have a total cost greater than 10
Order.filter_cost('gt', 10)

This is what I formed so far, but it doesn't seem to be entirely working. I'm thinking I'm not supposed to group, but it errors if I don't use grouping. With this following scope, I get a response, but the collection that is returned is not accurate.

scope :filter_cost, (lambda do |*args|
  if args[0] && %w(gt lt et).include?(args[0]) && args[1]
    operator_map = { 'gt' => '>', 'lt' => '<', 'et' => '=' }
    joins(order_items: :product).group('orders.id').group('order_items.id').group('products.id').having("SUM(products.price * order_items.quantity) #{operator_map[args[0]]} ?", args[1].to_f)
  end
end)

Can I get some guidance on what's wrong with my query?

Kkulikovskis Kkulikovskis · Accepted Answer · 2016-08-25T19:48:25

def self.total_cost(direction, amount)
  select(orders.*, SUM(products.price * order_items.quantity) AS total_price).
  joins(order_items: :product).
  where(SUM(products.price * order_items.quantity) #{direction} #{amount})
end

now you can use this like Order.total_cost(:>, 10) P.S. - you can even access the total_amount by calling it as a method on an instance returned by this query.

Like Order.total_cost(:>, 10).first.total_cost (returns total cost for that item)

I think that using signs instead of gt and lt is better since it is more straight forward for anyone to what that method does + You don't have to do unnecessary mapping.

Might not be the most elegant solution, but it works.

Advanced ActiveRecord Query: Filter Sum through Nested Associations

1 Answers