Rails where condition using NOT NIL

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.

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")

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))

Not sure of this is helpful but this what worked for me in Rails 4

Foo.where.not(bar: nil)

For Rails4:

So, what you're wanting is an inner join, so you really should just use the joins predicate:

  Foo.joins(:bar)

  Select * from Foo Inner Join Bars ...

But, for the record, if you want a "NOT NULL" condition simply use the not predicate:

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

Select * from Foo Left Outer Join Bars on .. WHERE bars.id IS NOT NULL

Note that this syntax reports a deprecation (it talks about a string SQL snippet, but I guess the hash condition is changed to string in the parser?), so be sure to add the references to the end:

Foo.includes(:bar).where.not(bars: {id: nil}).references(:bar)

> DEPRECATION WARNING: It looks like you are eager loading table(s) (one > of: ....) that are referenced in a string SQL snippet. For example: > > Post.includes(:comments).where("comments.title = 'foo'") > > Currently, Active Record recognizes the table in the string, and knows > to JOIN the comments table to the query, rather than loading comments > in a separate query. However, doing this without writing a full-blown > SQL parser is inherently flawed. Since we don't want to write an SQL > parser, we are removing this functionality. From now on, you must > explicitly tell Active Record when you are referencing a table from a > string: > > Post.includes(:comments).where("comments.title = 'foo'").references(:comments)

With Rails 4 it's easy:

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

See also: http://guides.rubyonrails.org/active_record_querying.html#not-conditions