Ruby-on-rails Rails where 条件使用 NOT NIL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4252349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails where condition using NOT NIL
提问by SooDesuNe
Using the rails 3 style how would I write the opposite of:
使用 rails 3 样式，我将如何编写相反的内容：
Foo.includes(:bar).where(:bars=>{:id=>nil})
I want to find where id is NOT nil. I tried:
我想找到 id 不为零的地方。我试过：
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.
这绝对不是我需要的，而且几乎看起来像是 ARel 中的一个错误。
回答by Adam Lassek
The canonical way to do this with Rails 3:
使用 Rails 3 执行此操作的规范方法：
Foo.includes(:bar).where("bars.id IS NOT NULL")
ActiveRecord 4.0 and above adds where.notso you can do this:
ActiveRecord 4.0 及更高版本添加，where.not因此您可以执行以下操作：
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 mergeso that I can use existing scopes more easily.
在使用表之间的范围时，我更喜欢利用，merge以便我可以更轻松地使用现有范围。
Foo.includes(:bar).merge(Bar.where.not(id: nil))
Also, since includesdoes not always choose a join strategy, you should use referenceshere as well, otherwise you may end up with invalid SQL.
此外，由于includes并不总是选择连接策略，因此您也应该references在这里使用，否则最终可能会得到无效的 SQL。
Foo.includes(:bar)
.references(:bar)
.merge(Bar.where.not(id: nil))
回答by Ryan Bigg
It's not a bug in ARel, it's a bug in your logic.
这不是 ARel 中的错误，而是您逻辑中的错误。
What you want here is:
你想要的是：
Foo.includes(:bar).where(Bar.arel_table[:id].not_eq(nil))
回答by Matt Rogish
For Rails4:
对于 Rails4：
So, what you're wanting is an inner join, so you really should just use the joins predicate:
所以，你想要的是一个内部连接，所以你真的应该使用 joins 谓词：
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:
但是，对于记录，如果您想要“NOT NULL”条件，只需使用 not 谓词：
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:
请注意，此语法报告了弃用（它谈论的是字符串 SQL 片段，但我猜散列条件已更改为解析器中的字符串？），因此请务必将引用添加到末尾：
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)
弃用警告：看起来您正在急切加载在字符串 SQL 代码段中引用的表（其中之一：....）。例如：
Post.includes(:comments).where("comments.title = 'foo'")目前，Active Record 识别字符串中的表，并且知道将注释表加入到查询中，而不是在单独的查询中加载注释。但是，在不编写完整的 SQL 解析器的情况下执行此操作具有内在的缺陷。由于我们不想编写 SQL 解析器，因此我们将删除此功能。从现在开始，当你从一个字符串中引用一个表时，你必须明确地告诉 Active Record：
Post.includes(:comments).where("comments.title = 'foo'").references(:comments)
回答by Raed Tulefat
Not sure of this is helpful but this what worked for me in Rails 4
不确定这是否有帮助，但这在 Rails 4 中对我有用
Foo.where.not(bar: nil)
回答by Tilo
With Rails 4 it's easy:
使用 Rails 4 很容易：
Foo.includes(:bar).where.not(bars: {id: nil})
See also: http://guides.rubyonrails.org/active_record_querying.html#not-conditions
另见：http: //guides.rubyonrails.org/active_record_querying.html#not-conditions
