CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3activerecord
David Tuite
David Tuite

Reputation: 22643

Where NOT IN () on a join?

I have the following scope in a Rails model.

class Suggestion < ActiveRecord::Base
  has_many :favourites

  def self.favoured_by(user)
    joins(:favourites).where(favourites: { user_id: user.id })
  end
end

That works perfectly. It will return all the suggestions which a particular user has favourited.

How can I retrieve all the suggestions which are either not favourited at all or which are favourited but not by this particular user?

def self.not_favoured_by(user)
  # ...
end

My Favourite model looks like this:

class Favourite < ActiveRecord::Base
  belongs_to :suggestion
  belongs_to :user
end

Upvotes: 1

Views: 2641

Answers (2)

Ismael
Ismael

Reputation: 16720

favorited_suggestions_ids = joins(:favourites).where(favourites: { user_id: user.id }).map(&:id)
return scoped if favorited_suggestion_ids.empty?
where('id not in (?)',favorited_suggestions_ids)

Upvotes: 4

Cody Caughlan
Cody Caughlan

Reputation: 32748

How about this:

def self.not_favoured_by(user)
  sql = <<-SQL
    SELECT suggestions.* FROM suggestions 
    WHERE NOT EXISTS 
      (SELECT id FROM favourites WHERE user_id = #{user.id} AND favourites.suggestion_id = suggestions.id);
  SQL
  find_by_sql(sql)
end

Upvotes: 0

Related Questions