console.rb

> Product.best_sellers
'SELECT `products`.*, COUNT(*) AS total FROM `products` INNER JOIN order_items ON order_items.product_id = products.id INNER JOIN orders ON orders.id=order_items.order_id WHERE ((orders.payment_state=3) GROUP BY products.id ORDER BY total DESC'
# Yay!

> @store.products.best_sellers
'SELECT `products`.*, COUNT(*) AS total FROM `products` INNER JOIN order_items ON order_items.product_id = products.id INNER JOIN orders ON orders.id=order_items.order_id WHERE (`products`.store_id = 464) AND (orders.payment_state=3) GROUP BY products.id ORDER BY products.name ASC'
# Nooo!!!

product.rb

class Product < ActiveRecord::Base
  belongs_to :store

  default_scope :order => "products.name ASC"

  named_scope :best_sellers, {:select => "`products`.*, COUNT(*) AS total", 
       :conditions => "orders.payment_state=#{Order::PaymentState::APPROVED}", 
       :group => "products.id",
       :joins => "INNER JOIN order_items ON order_items.product_id = products.id INNER JOIN orders ON orders.id=order_items.order_id",
       :order => "total DESC"}
end

store.rb

class Store < ActiveRecord::Base
  has_many :products, :dependent => :destroy
end