扶手：如何查询的每一个协会有一个属性的所有对象，是不是空 [英] RAILS: How to query for all the objects whose every association have an attribute that is not null

问题描述

进出口工作on Rails的3.0.5和PostgreSQL。

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

该产品具有一个ID，当你在一个第三方服务进行注册，更新。让我们把它叫做 service_id为。我要报价范围，即获取所有那些在第三方服务注册的每一个产品的报价。换句话说，我只关心在要约例如，如果所有的产品都有一个服务ID。

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.

招募范围：连接（：产品）。凡（products.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.

任何帮助将是AP preciated。

Any help would be appreciated.

（PS：很抱歉的冠军，我试图解释它尽我所能。）

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

编辑：的

例如：如果我有2报价：Offer1和Offer2。 Offer1具有产品p1和p2，和Offer2具有产品P3和P4。比方说，P1，P2和P3具有服务，但P4没有。该查询应该只返回Offer1。

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.

推荐答案

这是另一种方法。

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)")

即使做一个ORM的想法是，你抽象的SQL，在这种复杂的查询，我觉得这是更好地挑​​最简单的解决方案，而不是更复杂的查询。

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.

这篇关于扶手：如何查询的每一个协会有一个属性的所有对象，是不是空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！