CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Andy Smart
Andy Smart

Reputation: 725

Finding nil has_one associations in where query

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

Upvotes: 46

Views: 17086

Answers (4)

Rishabh
Rishabh

Reputation: 263

Rails 6.1 has added a query method called missing in the ActiveRecord::QueryMethods::WhereChain class.

It returns a new relation with a left outer join and where clause between the parent and child models to identify missing relations.

Example:

Item.where.missing(:purchase)

Upvotes: 8

ReggieB
ReggieB

Reputation: 8212

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

Upvotes: 16

bronson
bronson

Reputation: 5992

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.

Upvotes: 34

dimuch
dimuch

Reputation: 12818

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

Upvotes: 37

Related Questions