Rails where condition with nil value

user_536711 0
Viewed 51598

I found out that using where with symbol :my_id => nil and using old school one with ? is different. Could anyone explain me why?

MyTable.where("my_id = ? ", nil).first
SELECT `my_tables`.* FROM `my_tables` WHERE (my_id = NULL ) LIMIT 1

Does not get any data

MyTable.where(:my_id => nil).first
SELECT `my_tables`.* FROM `my_tables` WHERE (`my_tables`.`my_id` IS NULL) LIMIT 1

Get data which has my_id is null.

What is the best practise to use in rails?

I think I didn't make clear about my question. In my rails application, request parameter is nil. Existing coding is MyTable.where(:my_id => params[:id]).first In table, there are lots of records which have my_id is null. Therefore, the first record from table is pick up without realizing. First of all, yes it is the problem with unclean data in table.

To solve this problem. I find two solutions

Solution 1

if params[:id].present?
  MyTable.where(:my_id => params[:id]).first
end

Solution 2

MyTable.where("my_id = ? ", nil).first

As you know, if we put (if condition more and more), our application will get slower and it will not be functional programming. When I try solution 2, I get surprised because I am expecting it should give same result.

null ruby-on-rails
5 Answers

For any relational database the predicate (my_id = NULL ) is always false, because NULL never equals anything - not even NULL.

A more complete explanation is here https://dba.stackexchange.com/questions/166579/why-is-null-ignored-in-query-results/166586#166586.

ActiveRecord will cope with this very well, even when null is included in an array.

irb(main):029:0> Book.where(id: 1).to_sql
=> "SELECT ... WHERE \"books\".\"id\" = 1"
irb(main):030:0> Book.where(id: nil).to_sql
=> "SELECT ... WHERE \"books\".\"id\" IS NULL"
irb(main):031:0> Book.where(id: [1, nil]).to_sql
=> "SELECT ... WHERE (\"books\".\"id\" = 1 OR \"books\".\"id\" IS NULL)"
David_Aldridge_67420
Related