CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Michael Alexander
Michael Alexander

Reputation: 41

.where.not with empty array

I have a problem trying to work with a NOT IN query (using Rails 4/Postgres, for reference) in an elegant way. I'm trying to get a list of all objects of a certain model that don't show up in a join table for a certain instance. It works , when you try a NOT IN query with an empty array, it throws an error because you can't look for NOT IN NULL.

The below code now works, but is there a better way than to use an unintuitive conditional to make a pseudo-null object?

def characters_selected
  self.characters_tagged.pluck(:name)
end

def remaining_characters
  characters = self.characters_selected
  characters = ["SQL breaks if this is null"] if characters.empty?

  # this query breaks on characters_selected == [] without the above line
  Character.where("name NOT IN (?)", characters ) 
end

Upvotes: 2

Views: 1046

Answers (1)

johnsorrentino
johnsorrentino

Reputation: 2731

This is the ActiveRecord way:

def remaining_characters
  characters = self.characters_selected
  Character.where.not(:name => characters) 
end

When characters.empty? the where clause becomes "WHERE (1=1)".

Upvotes: 1

Related Questions