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

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

Answer 2

Found two other railsey ways of doing this:

Item.includes(:purchase).references(:purchase).where("purchases.id IS NULL")

Item.includes(:purchase).where(purchases: { id: nil })

Technically the first example works without the 'references' clause but Rails 4 spits deprecation warnings without it.

Answer 3

A more concise version of @dimuch solution is to use the left_outer_joins method introduced in Rails 5:

Item.left_outer_joins(:purchase).where(purchases: {id: nil})

Note that in the left_outer_joins call :purchase is singular (it is the name of the method created by the has_one declaration), and in the where clause :purchases is plural (here it is the name of the table that the id field belongs to.)