CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqlactiverecord
e3matheus
e3matheus

Reputation: 2132

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 < ActiveRecord::Base
   belongs_to :offer
end

The product has an id that updates when you register it in a third party service. Lets call it service_id. I want an Offer scope, that gets all the offers that have every single product registered in the third party service. In other words, I'm only interested in the offer instance if all of its products have a service id.

The offer scope: joins(:products).where("products.service_id is not NULL"), just returns the offers that have at least one product that is not null.

Any help would be appreciated.

(PS: Sorry for the title, I tried to explain it the best I could.)

EDIT:

For example: If I have 2 offers: Offer1 and Offer2. Offer1 has products p1 and p2, and Offer2 has products p3 and p4. Let's say p1, p2 and p3 have a service but p4 doesn't. The query should return only Offer1.

Upvotes: 2

Views: 1634

Answers (2)

e3matheus
e3matheus

Reputation: 2132

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.

Upvotes: 3

MissingHandle
MissingHandle

Reputation: 693

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

class Product < ActiveRecord::Base

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

end

class Offer < 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?

Upvotes: 1

Related Questions