Rails scope testing for nil

Question

I have an expenses table. It has a column invoice_id.

I would like 2 scopes - one for billed expenses and one for not billed. I thought I could just test to see if invoice_id was nil.

scope :notbilled, where(:invoice_id == nil)
scope :billed, where(:invoice_id != nil)

But, that doesn't work.

Any ideas?

Thanks!

Answer 1

For the first one, you need to use hash syntax:

scope :notbilled, where(:invoice_id => nil)

The second requires you to use raw sql:

scope :billed, where('invoice_id is not null')

You can also jump down into arel for the second one to avoid writing raw sql, but that's an advanced topic and would end up less readable than the raw sql.

Using direct equality won't work, because those expressions are evaluated immediately, and are turned into their boolean equivalents because no variables are involved, so they would be interpreted as

scope :notbilled, where(false)
scope :billed, where(true)

Answer 2

Updated answer as of Rails 4:

scope :notbilled, where(invoice_id: nil)
scope :billed, where.not(invoice_id: nil)

This is a new feature added in ActiveRecord 4.

Docs: http://edgeguides.rubyonrails.org/active_record_querying.html (search for "NOT Conditions", currently section 2.4)