Rails: Find rows without connection in HABTM relation

has-and-belongs-to-many, ruby, ruby-on-rails

Solution

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

There are three standard ways to accomplish this,

- Using a null left outer join

- Using a where clause with a sub query with the `NOT` & `IN` keywords

- 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)

Problem

Original source