Rails 4: OR in where clause not working with querying nil

Question

I am trying to query any Company in my database where the field visible is either NULL or true. Here is what I found on a Stackoverflow post:

@companies = Company.where('visible=? OR visible=?', nil, true).page(params[:page]).per(10)

Somehow though, this does not seem to work for querying nil. When I use this code, displaying all companies where visible is nil works very well though.

@companies = Company.where('visible' => nil).page(params[:page]).per(10)

I would very much appreciate any ideas here.

Thanks!

EDIT:

This still displays only companies where visible is nil:

@companies = Company.where('visible is ? OR visible=?', nil, true).page(params[:page]).per(10)

kiddorails · Accepted Answer · 2016-08-24 19:07:10Z

2

That's because Company.where('visible=?', nil) makes query:

Company Load (0.3ms) SELECT companies.* FROM companies WHERE (visible = NULL)

In SQL, to compare with NULL, = doesn't work. It requires IS instead.

Company.where('visible is ?', nil) should do the trick for you. Add or statement along with it.

Company Load (0.3ms) SELECT companies.* FROM companies WHERE (visible is NULL)

OR, the perfect way:

Company.where(:visible => [true, nil])

answered Aug 24, 2016 at 19:07

kiddorails

13k2 gold badges34 silver badges42 bronze badges

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

7 Comments

patrick Over a year ago

Thank you so much for your answer. I just tried this out and updated my question. Even though I am now using "is" for my nil query, I only see companies where visible is nil.

kiddorails Over a year ago

visible is boolean field, right? Can you check your SQL for values and double-check if it shows 0 for false, 1 for true and null for absence? true in query translates to 1 for boolean field.

patrick Over a year ago

Visible is indeed a boolean field. I have one test company with true and all the others currently have NULL in the database.

kiddorails Over a year ago

Can you verify if this queries gives you the right rows in your sql : SELECT companies.* FROM companies WHERE (visible is NULL or visible is 1). If yes, then there is no reason why ActiveRecord shouldn't :). If not, please check the data.

patrick Over a year ago

WHERE (visible is NULL or visible is true) - this gives me the correct rows

|

egoholic · Accepted Answer · 2016-08-26 05:27:32Z

1

The problem is that you have no NOT NULL constraint in your DB.

You can also use conditional expressions(CASE) or function COALESCE.

edited Aug 26, 2016 at 5:27

answered Aug 24, 2016 at 23:07

egoholic

3171 silver badge7 bronze badges

2 Comments

patrick Over a year ago

How would I go about adding this?

egoholic Over a year ago

For new tables and column you need to add null: false option. For already existing tables/columns you need to make ALTER TABLE or to use AR's change_table method. Here is stupid simple example: ``` create_table :my_table do |t| t.column :title, :string, null: false end ```

Collectives™ on Stack Overflow

Rails 4: OR in where clause not working with querying nil

2 Answers 2

7 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Related