Rails associations NOT EXISTS. Better way?

Question

Using Rails 3.2.9
I'm attempting to get a list of items that are tied to a organization that do NOT have a owner.

I was able to get a array list using the below but just seems ugly to me. Is there a better way to do this?

Items.all(:select => "items.id, items.name",
  :joins => "INNER JOIN organizations on items.organization_id = organizations.id",
  :conditions => "NOT EXISTS (select * from items k JOIN items_owners on items.id = items_owners.item_id) and items.organization_id = 1")

Table Setup:
owners:

id
name

items:

id
name
organization_id

items_owners:

owner_id
item_id

organizations:

id
List item

Models:

class Organization < ActiveRecord::Base
   attr_accessible :name

   has_many :items
end

class Item < ActiveRecord::Base
   attr_accessible :description, :name, :owner_ids, :organization_id

   has_many :items_owner
   has_many :owners, :through => :items_owner
   belongs_to :organization
end

class Owner < ActiveRecord::Base
   attr_accessible :name

   has_many :items_owner
   has_many :items, :through => :items_owner
end

class ItemsOwner < ActiveRecord::Base
   attr_accessible :owner_id, :item_id

   belongs_to :item
   belongs_to :owner
end

I think an Item should belongs_to :organization, and Organization has_many :items, would have more sense — Dario Barrionuevo, Nov 21 '12 at 15:50

244an · Accepted Answer · 2022-05-20T18:21:56.507

11

Items.joins(:organization).includes(:owners).references(:owners).
  where('owners.id IS NULL')

And if you want to use includes for both:

Items.includes(:organization, :owners).references(:organization, :owners).
  where('organisations.id IS NOT NULL AND owners.id IS NULL')

And as @Dario Barrionuevo wrote, it should be belongs_to :organisation in Item.

Using arel_table in the first example:

Items.joins(:organization).includes(:owners).references(:owners).
  where(Owner.arel_table[:id].eq(nil))

In Rails 5 (from comment by @aNoble):

Items.joins(:organization).left_joins(:owners).
  where(Owner.arel_table[:id].eq(nil))

But using includes is still preferable if the relations should be referenced in the code, to avoid extra reads.

edited May 20 '22 at 18:21

answered Nov 21 '12 at 16:00

244an

1,579
11
15

Both of these worked except I had to remove all. the SQL output looks crazy but it works. Thanks – Sinble Nov 21 '12 at 16:46
You mean removing ".all" in both suggestions? in that case it's better if I change my suggestion. – 244an Nov 21 '12 at 19:21
In never versions on Rails (I'm testing on 5.2) you should use `left_joins` instead of `includes`. – aNoble Sep 13 '18 at 21:38
Thanks @aNoble, I have updated my answer. But in some situations it can be better to use includes (perhaps there is another way of doing that in Rails 5?). – 244an Sep 15 '18 at 22:34

score 5 · Answer 2 · answered Oct 19 '20 at 22:40

There are a number of ways to do NOT EXISTS in rails 5, 6:

distinct items OUTER JOIN item_owners where item_owners.id is null
items.id NOT IN (select item_id from item_owners)
NOT EXISTS (select 1 from item_owners where item_id = items.id)
where (select COUNT(*) from item_owners where item_id = items.id) = 0

Off my head I can think of 4 approaches, but I seem to remember there being 7. Anyway, this is a tangent but may give you some ideas that work better for your use case.

I found using the NOT IN approach was the easiest for my team to create and maintain. Our goals were to avoid arel, support WHERE clauses in the owner table (e.g.: admin owner), and supporting multiple levels of rails :through.

Items.where.not(id: Items.joins(:owners).select(:id))
     .select(:id, :name)

Items.where.not(id: Items.joins(:items_owners).select(:id))
     .select(:id, :name)

Items.where.not(id: ItemOwners.select(:item_id))

We use the first, but those examples should be in order from least optimized to best. Also in order from least knowledge of the models to the most.

score -1 · Answer 3 · answered Nov 21 '12 at 15:37

-1

Try this

Items.joins(:organisations).where(Items.joins(:items_owners).exists.not).select('items.id,items.name')

answered Nov 21 '12 at 15:37

Pritesh Jain

9,106
4
37
51

4

This is close. Below is the SQL OUTPUT. The problem is the scope of the last join is using items.id from the current select instead of the top select. SELECT items.id,items.name FROM "items" INNER JOIN "organizations" ON "organizations"."id" = "items"."organization_id" WHERE (NOT (EXISTS (SELECT "items".* FROM "items" INNER JOIN "items_owners" ON "items_owners"."item_id" = "items"."id"))) – Sinble Nov 21 '12 at 16:33

Rails associations NOT EXISTS. Better way?

3 Answers3