Rails ActiveRecord query where.not(field: [array]) is ignoring rows with nil strings?

Question

I have a simple DB query that is checking if a particular field does not contain 3x strings:

scope :not_abc, -> {
    where.not(name: ['A', 'B', 'C'])
}

The field in question is just a string with no default value, from schema.rb:

t.string   "name",     limit: 255

The problem is that the query above only returns fields that are non-nil, for example:

Returned objects:

name = ""
OR
name = "<any string NOT A,B,C>"

Ignored objects:

name = nil
OR
name = "A" or "B" or "C"

Is there any way to build a rails query that searches with an array of strings but includes nil values in the results?

What should be returned:

name = nil
OR
name = ""
OR
name = "<any string NOT A,B,C>"

where("name NOT IN (?) OR name = ?" ['A', 'B', 'C'], nil) add this. — Arup Rakshit
– Arup Rakshit, Commented Aug 19, 2016 at 17:43
@Arup Rakshit: Unfortunately, that returned the same results as where.not(name: ['A', 'B', 'C']) — kush
– kush, Commented Aug 19, 2016 at 17:49

pimpin · Accepted Answer · 2017-10-11 13:16:23Z

2

Got the same issue and found the solution here

Here is your scope in a version that should work.

scope :not_abc, -> {
    where("name NOT IN (?) OR name IS NULL", ['A', 'B', 'C'])
}

answered Oct 11, 2017 at 13:16

pimpin

3072 silver badges13 bronze badges

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

1 Answer 1