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.

`!nil` evaluates to `true` in Ruby, and ARel translates `true` to `1` in a SQL query. So the generated query is in fact what you asked for - this was not an ARel bug. — yuval, Feb 10 '17 at 00:25

score 561 · Accepted Answer · edited Feb 10 '22 at 14:02

561

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

edited Feb 10 '22 at 14:02

notapatch

6,569
6
41
45

answered Nov 23 '10 at 03:51

Adam Lassek

35,156
14
91
107

1

Last one here isn't working for me, do we need an extra gem or plugin for this? I get: `rails undefined method 'not_eq' for :confirmed_at:Symbol`.. – Tim Baas Jun 07 '11 at 10:21
3

@Tim Yes, the MetaWhere gem I linked above. – Adam Lassek Jun 09 '11 at 02:25
1

@oreoshake MetaWhere/Squeel are well worth having, this is just a tiny facet. But of course a general case is good to know. – Adam Lassek Jul 26 '11 at 22:26
Hi Adam/everyone, do you need both squeel and metawhere to do that nice "ruby only" stuff or is one of them enough? What's the difference, really? – mjnissim Jul 24 '12 at 15:56
And another thing: I have not managed to do this with sqlite3. sqlite3 wants to see `field_name != 'NULL'`. – mjnissim Jul 24 '12 at 16:36
@mjnissim MetaWhere was created for Rails 3.0, Squeel replaced it for Rails >= 3.1 – Adam Lassek Jul 25 '12 at 00:07
I personally try to avoid EVER having raw SQL in my code, so I use Squeel and also the new Arel syntax as explained by Ryan Bigg below – jaydel May 31 '16 at 13:45
@jaydel the problem with Squeel is that it needs constant attention to deal with breaking changes, and it doesn't always have an owner. I'm transitioning more toward lightweight Arel helpers and query objects instead. – Adam Lassek May 31 '16 at 21:04
@AdamLassek You can really say "it doesn't always have an owner" to most gems in the ruby world. I think that most of us decide to either trust a gem (to a degree) or accept the risk that you might have to fork and fix things you need. – jaydel Jun 09 '16 at 14:17
@AdamLassek thank you Adam. What if there are multiple where conditions? would appending the rest of them .where( other conditions here) not hamper performance? – BenKoshy Aug 19 '16 at 02:01
1

@BKSpurgeon Chaining `where` conditions is simply building an AST, it doesn't hit the database until you hit a terminal method like `each` or `to_a`. Building the query isn't a performance concern; what you're requesting from the database is. – Adam Lassek Aug 20 '16 at 00:00

score 254 · Answer 2 · answered Nov 23 '10 at 03:53

254

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

answered Nov 23 '10 at 03:53

Ryan Bigg

106,965
23
235
261

2

I'm curious what the logic is then for turning !nil into '1' – SooDesuNe Nov 23 '10 at 05:07
12

At a guess, !nil returns `true`, which is a boolean. `:id => true` gets you `id = 1` in SQLese. – zetetic Nov 23 '10 at 05:40
This is a good way to avoid writing raw sql fragments. The syntax isn't as concise as Squeel though. – Kelvin May 16 '12 at 16:14
1

I have not managed to do this with sqlite3. sqlite3 wants to see `field_name != 'NULL'`. – mjnissim Jul 24 '12 at 16:35
@zetetic Unless you're using postgres, in which case you get `id = 't'` :) – thekingoftruth Sep 11 '12 at 08:47
The problem with this is that it != null always tests true in SQL even if it IS null. Yes, the rails folks have worked around this but using the not_eq syntax just encourages confusion when you eventually do need to do the conversion to SQL. – masukomi Nov 08 '12 at 20:54
Worth noting that this works in Rails 3 (unlike several answers). – Kyle McClellan Apr 23 '20 at 15:35

score 47 · Answer 3 · answered Nov 11 '16 at 12:03

47

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

Foo.where.not(bar: nil)

answered Nov 11 '16 at 12:03

Raed Tulefat

671
6
5

2

This is the best answer here. -- 2017 https://robots.thoughtbot.com/activerecords-wherenot – dezman Jul 21 '17 at 20:02

score 38 · Answer 4 · answered Oct 06 '13 at 18:36

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)

The `references` call helped me! – theblang Jan 20 '17 at 02:38 — theblang, Jan 20 '17 at 02:38

score 23 · Answer 5 · answered Aug 08 '14 at 06:12

23

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

answered Aug 08 '14 at 06:12

Tilo

33,354
5
79
106

Rails where condition using NOT NIL

5 Answers5

Rails 4+

Rails 3

Linked

Related