CODEWITHSUNDEEP
ruby-on-rails-3scopehas-and-belongs-to-many
Eric Koslow
Eric Koslow

Reputation: 2064

Rails find where HABTM association is nil

I have Popup and User that have a HABTM association.

I am trying to write a scope for Popup that would look like: Popup.not_seen_by(current_user).first

Unfortunately, my implementation below doesn't work. I feel like I'm on the right track, would anyone be willing to help me get over this last hurdle?

class Popup < ActiveRecord::Base
  has_and_belongs_to_many :users, :uniq => true

  scope :not_seen_by, lambda { |user| 
    # This does NOT work
    joins("LEFT JOIN popups_users ON popups.id = popups_users.popup_id").
    where("popups_users.user_id != ?", user.id)
  }
end

Thank you!

Upvotes: 1

Views: 557

Answers (1)

griswoldbar
griswoldbar

Reputation: 499

you are on the right track, but you are joining to every single row in popups_users that has a matching popup_id (i.e. a row that does not contain your user_id), and so returning multiple instances of the same popup. this should be what you're looking for:

scope :not_seen_by, lambda { |user| where('id NOT IN (select popup_id 
                                                  from popups_users 
                                                  where user_id = ?)',user.id) }

Not quite sure why my original 'IN' version didn't work. NOT IN is usually pretty slow, so you might want to try this too which should be equivalent and may be faster:

scope :not_seen_by, lambda { |user| where('NOT EXISTS (select 1 
                                                  from popups_users
                                                  where popups_users.popup_id = popups.id
                                                  and user_id = ?)',user.id) }

Upvotes: 2

Related Questions