ruby on rails - Finding nil has_one associations in where query

Question

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

question from:https://stackoverflow.com/questions/12515268/finding-nil-has-one-associations-in-where-query

Answer 1

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