Active Record - Get records where association doesn't exists

Question

I have a model A which has_many of my B model (so the B model references the A with a foreign_key a_id). I would like to get all A records which are not referenced by any B records. Which is the more efficient way to do that with active record? Thanks!

m. simon borg · Answer 1 · 2017-08-08T21:08:44.120

4

As of Rails 5:

A.left_outer_joins(:bs).where(bs: { a_id: nil })

The output of the SQL is:

SELECT "as".* FROM "as"
LEFT OUTER JOIN "bs" ON "bs"."a_id" = "a"."id"
WHERE "bs.a_id" IS NULL

edited Aug 08 '17 at 21:08

answered Aug 08 '17 at 16:57

m. simon borg

2,515
12
20

1

Benchmark here https://stackoverflow.com/a/45574301/2009803 – olimart Oct 31 '17 at 14:26

score 3 · Accepted Answer · answered Aug 08 '17 at 16:57

3

The detail may depend on your database, indexes you created, and data you store but I'd recommend giving subqueries a try:

A.where.not(id: B.select(:a_id))

On PostgreSQL this will result in single query like:

SELECT * FROM as WHERE id NOT IN (SELECT a_id FROM bs)

answered Aug 08 '17 at 16:57

Greg Navis

2,818
10
10

This is exactly what I need! Gonna accept the answer soon. Thanks – Ronan Lopes Aug 08 '17 at 17:00
Thank you, @RonanLopes! If the query turns out to be too slow in your case please post an update and I'm happy to help you tune it. :-) – Greg Navis Aug 08 '17 at 17:41

score 0 · Answer 3 · answered Aug 08 '17 at 16:58

I am thinking that you your models look like

class A < ApplicationRecord
  has_may :bs
end

class B < ApplicationRecord
  belongs_to :a
end

I am thinking that your schema.rb looks like

  create_table "as", force: :cascade do |t|
    t.integer  "id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

  create_table "bs", force: :cascade do |t|
    t.integer  "id"
    t.integer  "a_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
  end

if I have this right then you should be able to do

A.where.not(id: B.select(:a_id))

Active Record - Get records where association doesn't exists

3 Answers3