Rails associations DO NOT EXIST. The best way?

Question

Using Rails 3.2.9
I am trying to get a list of items that are related to an organization that does NOT have an owner.

I was able to get a list of arrays using the ones below, but it 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:

• I'd
• title

Items:

• I'd
• title
• organization_id

items_owners:

• owner_id
• item_id

organizations:

• I'd
• 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

EDIT: Removed .all , added references and added use of arel_table

 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 belong

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 @aNoble comment):

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

But using includes is still preferable if links should be referenced in code to avoid extra readings.

Answer 2

try it

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