sql - 事件记录 where.not 返回空数组

Question

执行命令

Fight.last.fight_logs.where(item_id: nil)

生成sql:

Fight Load (0.3ms)  SELECT  "fights".* FROM "fights" ORDER BY "fights"."id" DESC LIMIT $1  [["LIMIT", 1]]
FightLog Load (0.2ms)  SELECT  "fight_logs".* FROM "fight_logs" WHERE "fight_logs"."fight_id" = $1 AND "fight_logs"."item_id" IS NULL LIMIT $2  [["fight_id", 27], ["LIMIT", 11]]

并返回:

#<ActiveRecord::AssociationRelation [#<FightLog id: 30, fight_id: 27, attack: 0, block: 0, item_id: nil, user_id: 1, damage: 11.0, created_at: "2017-11-02 16:20:55", updated_at: "2017-11-02 16:20:57">, #<FightLog id: 31, fight_id: 27, attack: 0, block: 0, item_id: nil, user_id: 20, damage: 3.0, created_at: "2017-11-02 16:20:57", updated_at: "2017-11-02 16:20:57">, #<FightLog id: 33, fight_id: 27, attack: 0, block: 0, item_id: nil, user_id: 1, damage: 1.0, created_at: "2017-11-02 16:21:40", updated_at: "2017-11-02 16:21:40">, #<FightLog id: 32, fight_id: 27, attack: 0, block: 0, item_id: nil, user_id: 20, damage: 7.0, created_at: "2017-11-02 16:21:33", updated_at: "2017-11-02 16:21:40">, #<FightLog id: 34, fight_id: 27, attack: 0, block: 0, item_id: nil, user_id: 1, damage: 12.0, created_at: "2017-11-02 16:21:47", updated_at: "2017-11-02 16:21:48">, #<FightLog id: 35, fight_id: 27, attack: 0, block: 0, item_id: nil, user_id: 20, damage: 14.0, created_at: "2017-11-02 16:21:48", updated_at: "2017-11-02 16:21:48">]>

但是

Fight.last.fight_logs.where.not(item_id: 1)

生成sql:

Fight Load (1.0ms)  SELECT  "fights".* FROM "fights" ORDER BY "fights"."id" DESC LIMIT $1  [["LIMIT", 1]]
FightLog Load (0.8ms)  SELECT  "fight_logs".* FROM "fight_logs" WHERE "fight_logs"."fight_id" = $1 AND ("fight_logs"."item_id" != $2) LIMIT $3  [["fight_id", 27], ["item_id", 1], ["LIMIT", 11]]

并返回:

#<ActiveRecord::AssociationRelation []>

这怎么可能？我做错了什么？

Answer 1

您应该在查询中指定 NULL 值，因为您的数据库中有它:

Fight.last.fight_logs.where('item_id != ? OR item_id IS NULL', 1)

这就是 SQL 的工作原理:

select 1 != NULL;
+-----------+
| 1 != NULL |
+-----------+
|      NULL |
+-----------+

可以看看this answer澄清问题。另外，我建议避免在你的数据库中使用默认的 NULL 值，有很好的 answer关于它。你可以简单地使用 default: 0, null: false 你的情况。