Find all records that don't have any of an associated model

Question

I'm using Rails 3.2.

I have a product model, and a variant model. A product can have many variants. A variant can belong to many products.

I want to make a lookup on the Products model, to find only products that have a specific variant count, like such (pseudocode):

Product.where("Product.variants.count == 0")

How do you do this with activerecord?

Answer 1

You can use a LEFT OUTER JOIN to return the records that you need. Rails issues a LEFT OUTER JOIN when you use includes.

For example:

Product.includes(:variants).where('variants.id' => nil)

That will return all products where there are no variants. You can also use an explicit joins.

Product.joins('LEFT OUTER JOIN variants ON variants.product_id = products.id').where('variants.id' => nil)

The LEFT OUTER JOIN will return records on the left side of the join, even if the right side is not present. It will place null values into the associated columns, which you can then use to check negative presence, as I did above. You can read more about left joins here: http://www.w3schools.com/sql/sql_join_left.asp.

The good thing about this solution is that you're not doing subqueries as a conditional, which will most likely be more performant.

Answer 2

products= Product.find(:all,:select => 'variant').select{|product| product.varients.count > 10}

This is rails 2.3 , but only the activeRecord part, you need to see the select part