ActiveRecord 'where' clause not working if filter array is empty

Question

I'd like to fetch all the Active Relations whose ID's are NOT in an array. Like this:

    @widgets = Widget.where("id not in (?)", [1, 2, 3])

This works fine. It returns the full Widget table except the those records excluded by the filter array. However, if the filter array is empty, then it doesn't work.

    @widgets = Widget.where("id not in (?)", [])

returns "[]", when really I would like the equivalent of Widget.all

I have worked around this by testing first if the filter array is empty, and modifying the query. But the workaround seems like a kludge. Is there a way to get express this 'where' clause so that it returns the entire table if the filter array is empty?

Tim Dorr · Accepted Answer · 2013-07-11 15:35:09Z

2

You can add a scope to the Widget model:

scope :excluding_ids, ->(ids) { where('id NOT IN (?)', ids) if ids.any? }

Then it's just a matter of doing this wherever you need it:

@widgets = Widgets.excluding_ids([1,2,3])

answered Jul 11, 2013 at 15:35

Tim Dorr

4,9412 gold badges23 silver badges24 bronze badges

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

3 Comments

Monty Over a year ago

Tried this, it works! It does not fix the underlying issue (why it's not ok to pass [] in the filter array), but adding the scope sheds light on the issue. I'm torn though, because Ross' code is simpler, and yet masking the issue with a filter array of '[0]' doesn't feel quite right.

Tim Dorr Over a year ago

ActiveRecord converts [] to NULL in SQL. So, you're basically running SELECT * FROM widgets WHERE id NOT IN (NULL), which causes the database to return no records.

Tim Dorr Over a year ago

Alternatively, if you don't want to do this in a scope: @widgets = ids.any? ? Widget.where("id NOT IN (?)", ids) : Widget.all Although I don't like that because it's rather hard to read (lots of question marks).

Alyssa Ross · Accepted Answer · 2013-07-11 15:23:04Z

1

I'm assuming your IDs start at 1, so you won't have a Widget with id=0. You could add a zero to the array, so it would never be empty, and it would return all widgets since none have id=0.

@widgets = Widget.where("id not in (?)", my_array + [0])

This is kind of a hack, but it should work!

answered Jul 11, 2013 at 15:23

Alyssa Ross

2,16916 silver badges26 bronze badges

5 Comments

Monty Over a year ago

That is exactly the workaround I did :-) But as you say, it's a hack.

maximus ツ Over a year ago

@Monty it is a hack, but this is not really a problem of rails. This bug exist at database level bugs.mysql.com/bug.php?id=12474

Monty Over a year ago

Thanks @maximus that sheds some light. I am using postgres, but the mysql explanation in that link seems to be based on the ANSI SQL standard for passing 'IN()'. Basically he is saying 'dont't query the db if you know the answer' - it's a waste of resources.

Monty Over a year ago

@Ross, I went with Tim's answer, but yours should get 'honorable mention'. I was using "filter_array = ids.empty? ? [0] : ids" and just tacking on " + [0]" is a "cleaner" hack :-)

Alyssa Ross Over a year ago

@Monty to be honest I'd go with Tim's answer as well. If I had to do this in multiple places it would definitely be more convenient to use a scope.

Collectives™ on Stack Overflow

ActiveRecord 'where' clause not working if filter array is empty

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related