CODEWITHSUNDEEP
ruby-on-railsnull
Swe
Swe

Reputation: 389

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.

Upvotes: 30

Views: 57528

Answers (5)

David Aldridge
David Aldridge

Reputation: 52396

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

Upvotes: 1

BookOfGreg
BookOfGreg

Reputation: 3716

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

MyTable.where(my_id: nil).first

Upvotes: 21

Mischa
Mischa

Reputation: 43318

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

Upvotes: 60

leenasn
leenasn

Reputation: 1486

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

Upvotes: 11

Bohdan
Bohdan

Reputation: 8408

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

Upvotes: 0

Related Questions