Rails 3 ActiveRecord query: find the number of this product that have been ordered by this user

Question

I have an order model that belongs_to :user, a product model, a join model order_product:

class OrderProduct < ActiveRecord::Base
  belongs_to :order
  belongs_to :product
end

The order_product model has a quantity column.

How can I find how many of a product p have been ordered by a user u? I would like to find a solution that uses rails 3's active record query syntax and takes place at the database level as much as possible please. I'd appreciate any help.

Answer 1

That should do the trick:

u.order_products.where(:product_id => p.id).sum(:quantity)

That's a single SQL query, good for displaying one product. If you're displaying multiple products, then you would group by product ID, thereby giving you a Hash of values, from which you do your lookups.