CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3activerecord
professormeowingtons
professormeowingtons

Reputation: 3514

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

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?

Upvotes: 3

Views: 1277

Answers (4)

Wes Gamble
Wes Gamble

Reputation: 787

If you want to pull products which have a specific non-0 number of variants, you could do that with something like this (admittedly untested):

Product.select('product.id, product.attr1_of_interest, ... product.attrN_of_interest, variant.id, COUNT(*)')
       .joins('variants ON product.id = variants.product_id')
       .group('product.id, product.attr1_of_interest, ... product.attrN_of_interest, variant.id')
       .having('COUNT(*) = 5')  #(or whatever number manipulation you want to do here)

If you want to allow for 0 products, you would have to use Sean's solution above.

Upvotes: 1

Sean Hill
Sean Hill

Reputation: 15056

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.

Upvotes: 5

jvnill
jvnill

Reputation: 29599

I don't know of any ActiveRecord way to do this but the following should help with your problem. The good thing about this solution is that everything's done on the db side.

Product.where('(SELECT COUNT(*) FROM variants WHERE variants.product_id = products.id) > 0')

Upvotes: 1

sunny1304
sunny1304

Reputation: 1694

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

Upvotes: 1

Related Questions