Rails where condition using NOT NIL

Question

Using the rails 3 style how would I write the opposite of:

Foo.includes(:bar).where(:bars=>{:id=>nil}) 

I want to find where id is NOT nil. I tried:

Foo.includes(:bar).where(:bars=>{:id=>!nil}).to_sql 

But that returns:

=> "SELECT     \"foos\".* FROM       \"foos\"  WHERE  (\"bars\".\"id\" = 1)" 

That's definitely not what I need, and almost seems like a bug in ARel.

Answer 1

Rails 4+

ActiveRecord 4.0 and above adds where.not so you can do this:

Foo.includes(:bar).where.not('bars.id' => nil) Foo.includes(:bar).where.not(bars: { id: nil }) 

When working with scopes between tables, I prefer to leverage merge so that I can use existing scopes more easily.

Foo.includes(:bar).merge(Bar.where.not(id: nil)) 

Also, since includes does not always choose a join strategy, you should use references here as well, otherwise you may end up with invalid SQL.

Foo.includes(:bar)    .references(:bar)    .merge(Bar.where.not(id: nil)) 

Rails 3

The canonical way to do this with Rails 3:

Foo.includes(:bar).where("bars.id IS NOT NULL") 

Answer 2

It's not a bug in ARel, it's a bug in your logic.

What you want here is:

Foo.includes(:bar).where(Bar.arel_table[:id].not_eq(nil))