Rails: Find rows without connection in HABTM relation

Question

I have two models, Users and Leads connected with HABTM relation:

class Lead < ActiveRecord::Base
  has_and_belongs_to_many :users
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :leads
end

How can I now get only those Leads which are not connected to Users?

Thanks in advance!

Answer 1

What you're looking for is known as an anti join .

There are three standard ways to accomplish this,

1. Using a null left outer join
2. Using a where clause with a sub query with the NOT & IN keywords
3. Using a where clause with the NOT & EXISTS keywords

Basically, the EXISTS keyword will check if any row is returned by the sub query and report that as a match, NOT obviously negates that true match.

here's my preferred way (using NOT & EXISTS)

class User < ActiveRecord::Base
  has_and_belongs_to_many :leads
  def self.without_leads
    where(<<-SQL)
      NOT EXISTS (SELECT 1 
        FROM   leads_users 
        WHERE  users.id = leads_users.user_id) 
    SQL
  end
end

class Lead < ActiveRecord::Base
  has_and_belongs_to_many :users
  def self.without_users
    where(<<-SQL)
      NOT EXISTS (SELECT 1 
        FROM   leads_users 
        WHERE  leads.id = leads_users.lead_id) 
    SQL
  end

 def self.not_connected_to(user)
    where(<<-SQL, user.id)
      NOT EXISTS (SELECT 1 
        FROM   leads_users 
        WHERE  leads.id = leads_users.lead_id
        AND leads_users.user_id = ?
        ) 
    SQL
  end
end

here's a non SQL approach using arel

class User < ActiveRecord::Base
  has_and_belongs_to_many :leads
  def self.without_leads
    habtm_table = Arel::Table.new(:leads_users)
    join_table_with_condition = habtm_table.project(habtm_table[:user_id])
    where(User.arel_table[:id].not_in(join_table_with_condition))
  end
end

class Lead < ActiveRecord::Base
  has_and_belongs_to_many :users
  def self.without_users
    habtm_table = Arel::Table.new(:leads_users)
    join_table_with_condition = habtm_table.project(habtm_table[:lead_id])
    where(Lead.arel_table[:id].not_in(join_table_with_condition))
  end
end

here is an example repo

Find users without leads

User.where(user_id: 1).without_leads

Find leads without users

Lead.without_users

Find leads not connected to a specific user

Lead.not_connected_to(user)

chain a sort

Lead.without_users.order(:created_at)