Rails where condition with nil value

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.

The correct SQL syntax is my_id IS NULL, so if you change your first snippet to the following it will work:

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

Both syntaxes are perfectly fine. It's up to your own preference. However, if it's a parameter and you don't know whether it will be nil or not, you'd better use:

MyTable.where(:my_id => parameter).first

In rails 4 you can specify the value as null using the new hash syntax.

MyTable.where(my_id: nil).first

For DB for NULL the syntax should be

my_id IS NULL

So you can give it as:

MyTable.where("my_id is NULL ").first

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

I suppose it's so called "rails magic" you can pass ranges

Client.where(:created_at => (Time.now.midnight - 1.day)..Time.now.midnight)

becomes

SELECT * FROM clients WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' 
                                                    AND '2008-12-22 00:00:00')

or if you pass a subset

Client.where(:orders_count => [1,3,5])

rails will do

SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5))

more