mysql - Rails 3 query on condition of an association's count

Question

In Rails 3 with mysql, suppose I have two models, Customers and Purchases, obviously purchase belongs_to customer. I want to find all the customers with 2 orders or more. I can simply say:

Customer.includes(:purchases).all.select{|c| c.purchases.count > 2}

Effectively though, the line above makes query on the magnitude of Customer.all and Purchase.all, then does the "select" type processing in ruby. In a large database, I would much prefer to avoid doing all this "select" calculation in ruby, and have mysql do the processing and only give me the list of qualified customers. That is both much faster (since mysql is more tuned to do this) and significantly reduces bandwidth from the database.

Unfortunately I am unable to conjure up the code with the building blocks in rails(where, having, group, etc) to make this happen, something on the lines of (psudo-code):

Customer.joins(:purchases).where("count(purchases) > 2").all

I will settle for straight MySql solution, though I much prefer to figure this out in the elegant framework of rails.

Answer 1

No need to install a gem to get this to work (though metawhere is cool)

Customer.joins(:purchases).group("customers.id").having("count(purchases.id) > ?",0)