Rails: Find Rows Without Connection in Habtm Relation

Rails: Find rows without connection in HABTM relation

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)

Rails find records by HABTM relation

This is what you need:

BlogPost.joins(blog_categories).where(blog_categories: { id: params[:id] })

Note that you can pass an array of ids via params[:id] if you need to look up multiple categories.

HABTM relation find all records, excluding some based on association

You can place a scope in your Project model like so:

scope :not_belonging_to, lambda {|user| joins(:projects_users).where('projects_users.user_id <> ?', user.id) }}

This assumes your join table name matches rails convention for HABTM associations

To then get projects that a user doesn't belong to, first find your user, then pass them to the scope like so:

@user = User.find(params[:id]) # example
@unowned_projects = Project.not_belonging_to(@user)

On reflection, that scope won't work as it will find projects that have more than one developer, if one of those is your guy.

Instead, use the following:

scope :not_belonging_to, lambda {|user| where('id NOT IN (?)', user.projects.empty? ? '' : user.projects) }

Rails habtm and finding record with no association

Your implicit join table would have been named groups_users based on naming conventions. Confirm it once in your db. Assuming it is:

In newer Rails version:

scope :not_in_any_group, -> {
    joins("LEFT JOIN groups_users ON users.id = groups_users.user_id")
    .where("groups_users.user_id IS NULL")
}

For older Rails versions:

scope :not_in_any_group, {
    :joins      => "LEFT JOIN groups_users ON users.id = groups_users.user_id",
    :conditions => "groups_users.user_id IS NULL",
    :select     => "DISTINCT users.*"
}

HABTM Relationship -- How Can I Find A Record Based On An Attribute Of The Associated Model

You need to include the interests table.

@events = Event.all(:include => :interests, :conditions => ["interests.id = ?", 4])

You had it right in your post, but you didn't pluralize interests.

Update

Because this answer is still getting attention, I thought it might be a good idea to update it using ActiveRecord::Relation syntax since the above way is going to be deprecated.

@events = Event.includes(:interests).where(interests: { id: 4 })

or

@events = Event.includes(:interests).where('interests.id' => 4)

And in case you want a custom condition

@events = Event.includes(:interests).where('interests.id >= 4')

Finding data through a habtm association

You can get the Tags that have Articles by:

Tag.joins(:articles)

Same applies for atricles that have tags.

Article.joins(:tags)

You may prefer using has_many through instead of habtm, that gives you more control over the join table, check this question

---

Related Topics