在where查询中找到nil has_one关联 [英] Finding nil has_one associations in where query

问题描述

这可能是一个简单的问题，但是我似乎正在拔头发寻找一种优雅的解决方案。我有两个ActiveRecord模型类，它们之间具有has_one和belongs_to关联：

This may be a simple question, but I seem to be pulling my hair out to find an elegant solution here. I have two ActiveRecord model classes, with a has_one and belongs_to association between them:

class Item < ActiveRecord::Base
  has_one :purchase
end

class Purchase < ActiveRecord::Base
  belongs_to :item
end

我在寻找查找所有与之没有购买对象关联的所有Item对象的理想方法，理想情况下，无需在Item上使用布尔 is_purchased 或类似属性。

I'm looking for an elegant way to find all Item objects, that have no purchase object associated with them, ideally without resorting to having a boolean is_purchased or similar attribute on the Item.

现在我有：

purchases = Purchase.all
Item.where('id not in (?)', purchases.map(&:item_id))

这行得通，但对我来说似乎效率不高，因为它正在执行两个查询（并且购买可能是一个庞大的记录集）。

Which works, but seems inefficient to me, as it's performing two queries (and purchases could be a massive record set).

Running Rails 3.1.0

Running Rails 3.1.0

推荐答案

这是很常见的任务，SQL OUTER JOIN通常可以很好地工作。看看此处，例如，

It's quite common task, SQL OUTER JOIN usually works fine for it. Take a look here, for example.

在这种情况下，请尝试使用

In you case try to use something like

not_purchased_items = Item.joins("LEFT OUTER JOIN purchases ON purchases.item_id = items.id").where("purchases.id IS null")

这篇关于在where查询中找到nil has_one关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！