.where.not with empty array

Question

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

johnsorrentino · Accepted Answer · 2014-11-24 23:46:25Z

1

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

answered Nov 24, 2014 at 23:46

johnsorrentino

2,7311 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

.where.not with empty array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related