Rails - conditional query with nil?

Question

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

Answer 1

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