!nil != not nil

by Damien on January 25, 2008

Here’s an odd one for you, and I leave it up to you to decide whether it is a bug or a feature.

A great little shortcut to be able to do in find methods on your models is for when you know a field is null you can just do e.g.:

Product.find_all_by_category_id_and_description(42, nil)

This will return a list of all products in category 42 with no description, as you can see from the debug output:

SELECT * FROM `products` WHERE (`products`.`category_id` = 42 AND `products`.`description` IS NULL)

Great, nice and simple.

So what if you want to find all products in category 42 that have a description? Normal logic might suggest something like this:

Product.find_all_by_category_id_and_description(42, !nil)

That logic works in Ruby itself, but Rails does something funny when it comes to piping that into a query:

SELECT * FROM `products` WHERE (`products`.`category_id` = 42 AND `products`.`description` = 1)

Huh? Well that doesn’t work. Given that Rails is smart enough to recognize that the “nil” passed in should get converted to “IS NULL” in the query, it’s disappointing that it doesn’t do likewise for “!nil”.

Bookmark and Share

Other Posts That Might Interest You

  1. When to go back in time and type SQL…
  2. Can you be *too* DRY?
  3. Expens’d Updates and the Current Roadmap

Tagged as: Ruby, ruby on rails

  • I'm trying to do the same thing, select all where description is not null. I Brians'S his code but I get an error. I ended up adding a boolean to the table that indicates whether its entered, but I would like to find a solution to select entries where properties are anything but nil!
  • Brian Terlson
    As other's have mentioned, Rails can't really be smart enough to notice the difference between true and !nil, since ruby considers them equal. The alternative is to not use the dynamic finders... find(:all, :conditions => ['category_id=42 and description IS NOT NULL']) for example.
  • The problem is that this would require handling boolean fields appropriately (as for them true should be handled as true) and possibly dealing with situations where the field is not nullable, as the programmer may be anticipating an = 1 there, as well. It's realizing that this is the same as typing find_all_by_category...(42, true) that complicates the issue. Perhaps if there was a constant NOT_NULL or something of the sort that was a particular type of object that could be recognized, that would make more sense.
  • I agree. !nil becomes 'true'. Perhaps you should write a patch so that true becomes IS NOT NULL. I think it would get popular support, although this is not a common usage.
blog comments powered by Disqus

Previous post:

Next post: