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

问题描述

这可能是一个简单的问题，但我似乎想在这里找到一个优雅的解决方案.我有两个 ActiveRecord 模型类，它们之间有 has_one 和 Beings_to 关联:

class Item 

我正在寻找一种优雅的方式来查找所有 Item 对象，这些对象没有关联的购买对象，理想情况下不需要在 Item 上使用布尔值 is_purchased 或类似属性.

现在我有:

purchases = Purchase.allItem.where('id 不在 (?)', purchase.map(&:item_id))

这行得通，但对我来说似乎效率低下，因为它执行两个查询(并且购买可能是一个庞大的记录集).

运行 Rails 3.1.0

解决方案

这是一项很常见的任务，SQL OUTER JOIN 通常适用于它.看看这里，例如.

在你的情况下尝试使用类似的东西

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

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

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.

Right now I have:

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

解决方案

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屋！