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.

回答1:

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

回答2:

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

MyTable.where(my_id: nil).first

回答3:

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

回答4:

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