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

Answer 1

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.

Answer 2

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

1. distinct items OUTER JOIN item_owners where item_owners.id is null
2. items.id NOT IN (select item_id from item_owners)
3. NOT EXISTS (select 1 from item_owners where item_id = items.id)
4. 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.