Rails not finding value using !=

Question

In Rails 3.0.9 (Ruby 1.9.2, MySQL) I have a method that is supposed to find users using two fields, type(string) and flag(boolean). The string part of the query works fine, but not the boolean part.

here's the code:

def find_users(type)
  @private_users = User.where('type =? and flag != ?', org_type, true)
end

to try to figure out what's going on, put this code:

@users.each do |f|
  puts "oh helllllllllllllllllllllllllllo #{f.user_name}"      
  puts "oh helllllllllllllllllllllllllllo #{f.flag}" 
  puts "oh helllllllllllllllllllllllllllo #{f.type}"
end

The flag field is blank/null for most of these and those are the ones I'm trying to pick up.

If I change the expression to ('type =? and flag = ?', type, true), it correctly finds the ones where 1 is the value of the flag.

I've tried these things to no avail:

 User.where('type =? and flag = ?', org_type, false)
 User.where('type =? and flag = ?', org_type, "")
 User.where('type =? and flag = ?', org_type, nil)

This probably an easy question for someone, so I hoping some knows the answer. Thank you!

Carlos Ramirez III · Accepted Answer · 2012-03-23 04:21:16Z

2

Try

User.where(:type => org_type, flag: nil)

Should result in the SQL query

SELECT "users".* FROM "users" WHERE "users"."type" = 'some_type' AND "users"."flag" IS NULL

The key being that ActiveRecord will use the IS NULL operator for your flag column.

answered Mar 23, 2012 at 4:21

Carlos Ramirez III

7,48430 silver badges35 bronze badges

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

Comments

Phil · Accepted Answer · 2012-03-23 04:17:34Z

1

If a nullable column is NULL, you can't use normal comparison operators. You need to use the IS NULL / IS NOT NULL operator.

You can also try the NULL-safe equals (<=>) operator (which is a new one to me too)

answered Mar 23, 2012 at 4:17

Phil

166k25 gold badges265 silver badges269 bronze badges

Collectives™ on Stack Overflow

Rails not finding value using !=

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related