activerecord search conditions - looking for null or false

Question

When doing a search in active record I'm looking for record's that do not have an archived bit set to true.

Some of the archived bits are null (which are not archived) others have archived set to false.

Obviously,

Project.all(:conditions => {:archived => false})

misses the projects with the archived bits with null values. How can all non-archived projects be selected wtih active record?

Answer 1

Try this:

Project.all(:conditions => 'archived IS NULL OR archived = FALSE')

This is a limitation of older versions of Rails, as explained here: https://rails.lighthouseapp.com/projects/8994/tickets/1181-ar-find-producing-null-when-it-should-be-is-null

Answer 2

Ron - I get the following error message from SQLite when I do as you suggest.

SQLite3::SQLException: no such column: FALSE: SELECT * FROM "review_phases" WHERE (closed IS NULL OR closed = FALSE)  LIMIT 20 OFFSET 0

Could it be that SQLite just handles booleans differently making this not work?

Answer 3

@metasoarous

Try:

Project.all(:conditions => "archived IS NULL OR archived = 'F'")

Answer 4

Why can I not comment on anything but my own answer? This is ridiculous. I shouldn't have to post another answer here just to continue discussion.

Anyway, that doesn't work for me. Neither does all of FALSE in parentheses. Is this seriously working for you folks? Could this be from SQLite behaving differently than MySQLor something?