Rails: How To Query For All The Objects Whose Every Association Have An Attribute That Is Not Null

Im working on Rails 3.0.5 and PostgreSQL. I have a model Offer, that has many Products. class Offer < ActiveRecord::Base has_many :products end class Product < ActiveRe

Solution 1:

This is another approach.

Offer.find_by_sql("SELECT * FROM offers o WHERE NOT EXISTS (SELECT * FROM products WHERE products.offer_id = o.id AND service_id IS NULL)")

Even do the idea of an ORM is that you abstract the SQL, in this kind of complex query, I think it is better to pick the simplest solution and not complicate the query even more.

Solution 2:

i believe what you want to do is define a scope on products and then join that scope to your scope Offer:

classProduct < ActiveRecord::Base

scope :serviced, where('products.service_id is not NULL')

endclassOffer < ActiveRecord::Base

scope :with_serviced_products, joins(:products) & Product.serviced

end

EDIT:

hmm, well now that i understand your question, I don't have a complete answer for you. just some thoughts. Sounds like you're going to need at least one sub-select querys and some Group Bys. What if..

You use rails to keep a counter-cache on your Offer model of products_count.

You count the number of serviced products for each Offer. Something like,

Product.serviced.group_by(:offer_id).count

And then select only the Offers where the two counts, it's products_count and the result of the sub-query, match?