CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
AnApprentice
AnApprentice

Reputation: 111090

Rails - conditional query with nil?

Any ideas what's wrong with this?

    @contacts = current_user.contacts.where("fname = ? OR lname = ?", nil, nil)

I want all contacts where either the fname or lname is empty.

Here is what it shows in the logs, not sure why it's not getting the records.

 Contact Load (0.6ms)  SELECT "contacts".* FROM "contacts" WHERE ("contacts".user_id = 2) AND (fname = NULL OR lname = NULL)

Thoughts?

Thanks

Upvotes: 7

Views: 6187

Answers (1)

Dylan Markow
Dylan Markow

Reputation: 124479

If you want empty (meaning a blank string, but not actually null), then use an empty string:

@contacts = current_user.contacts.where("fname = ? OR lname = ?", "", "")

Otherwise, if you truly want the null values, you need to use the is null wording:

@contacts = current_user.contacts.where("fname is null OR lname is null")

You can also use :lname => nil instead, but that format can't be used for OR queries. Note the different between "lname = ?", nil and :lname => nil:

@contacts = Contact.where("lname = ?", nil).to_sql
# => SELECT "contacts".* FROM "contacts" WHERE (lname = NULL)

@contacts = Contact.where(:lname => nil).to_sql
# => SELECT "contacts".* FROM "contacts" WHERE "contacts"."lname" IS NULL

Upvotes: 28

Related Questions