不应该`where.not(field: "something")` 包含`where(field: nil)`吗? [英] shouldn't `where.not(field: "something")` include `where(field: nil)`?

问题描述

也许我快疯了，或者只是需要休息一下，但是在我的 Rails 控制台中 Order.where(state: nil).count 返回 1010，但是 Order.where.not(state: "pending").count 返回 0... 如果订单的状态为 nil，那么它不是pending"，所以我期望not(state: "pending") 返回的集合包含集合 where(state: nil).

Perhaps I'm going crazy, or just need a break, but in my rails console Order.where(state: nil).count returns 1010, but Order.where.not(state: "pending").count returns 0... If an order's state is nil, then it is not "pending", so I expect the set returned by not(state: "pending") to include the set where(state: nil).

arel 不是这样工作的吗?如果没有，arel 是否以不同的方式工作?

Does arel not work this way? If not, does arel work a different way?

更多信息！当我转到另一个数据库时，其中一些记录的状态不是 nil，并且我运行 Order.where.not(state: "pending").count 我得到一堆订单，没有其中是待定"，但也没有一个是零.似乎 where.not 在查询中隐式添加了 而不是 nil?

more info! When I go to another database, where some records have a state other than nil, and I run Order.where.not(state: "pending").count I get back a bunch of orders, none of which are "pending" but also none of which are nil. It seems that where.not is implicitly adding a and not nil to the query?

在绝望中，我转向了更黑暗的灵魂.

in desperation, I have turned to darker spirits.

# look into another shop, that has records
o = Order.where(shop_id: 2)

# summon dread spirits
t = Order.arel_table[:state]

o.where(t.eq(nil).or(t.eq("pending").not)).count

=> 1569

o.where(t.eq(nil)).count

=> 1471

所以在这种情况下，我得到了 98 条状态既不是 nil 也不是pending"的记录，并且我得到了状态为 nil 的所有记录.我真的很想知道为什么我不能只说 where.not("pending") 并具有相同的效果.如果有我可以调用的选项?比如，where.not("pending", include_nil: true)?

So in this case, I get the 98 records whose status is neither nil nor "pending", and I get all the records whose status is nil. I would really like to know why I can't just say where.not("pending") and have the same effect. If there maybe an option I can invoke? Like, where.not("pending", include_nil: true)?

根据@Filip Bartuzi 在评论中的要求

as requested in a comment by @Filip Bartuzi

Order.where.not(state: "pending").to_sql

=> "SELECT \"orders\".* FROM \"orders\"  WHERE \"orders\".\"shop_id\" = 2 AND (\"orders\".\"state\" != 'pending')"

Orders.where(state: nil).to_sql

=> "SELECT \"orders\".* FROM \"orders\"  WHERE \"orders\".\"shop_id\" = 2 AND \"orders\".\"state\" IS NULL"

推荐答案

Order.where.not(state: "pending").to_sql 生成:

<代码>=>"SELECT \"orders\".* FROM \"orders\" WHERE \"orders\".\"shop_id\" = 2 AND (\"orders\".\"state\" != 'pending')"

它将返回所有带有 VALUES 且不是待处理"的记录.当您将 pending 设置为 nil (例如 Order.first.update! state: nil 它在数据库中分配 NULL.

It will return all records with VALUES which are not 'pending'. When u set pending to nil (like Order.first.update! state: nil it assigns NULL in database.

NULL 不会被解释为 SQL 中的值，因此它不会包含在 SELECT 响应中

NULL isn't interpreted as value in SQL so it will not be included in SELECT response

所以答案是:where.not(field: something") 不包括 where(field: nil)!

So the answer is : where.not(field: "something") does NOT include where(field: nil)!

你可以在这里查看它是如何工作的:

You can check how it works here:

http://www.w3schools.com/sql/trysql.asp?文件名=trysql_select_all

进入categories表，首先执行

Go to the categories table and firstly execute

UPDATE Categories
SET CategoryName = NULL
WHERE CategoryName = 'Beverages'

所以现在我们有了 Count 8 的类别，其中一个在 CategoryName

So now we have Categories of Count 8 which one of them has NULL on column CategoryName

现在执行:

SELECT CategoryName FROM Categories
WHERE CategoryName != 'Condiments'

如您所见，返回了 6 条记录(因此它跳过了一条 NULL)

As you see 6 records where returned (so it skipped one with NULL)

这篇关于不应该`where.not(field: "something")` 包含`where(field: nil)`吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！