Active record where.not returns empty array

Question

Following command

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

generates 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]]

and returns:

#<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">]>

but

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

generates 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]]

and returns:

#<ActiveRecord::AssociationRelation []>

How it is possible? What i'm doing wrong?

Ilya · Accepted Answer · 2017-11-03 05:52:01Z

3

You should specify NULL value in your query since you have it in your database:

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

This is just how SQL works:

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

You can look at this answer to clarify the issue. Also, I would recommend avoiding using default NULL values in your database, there is nice answer about it. You can simply use default: 0, null: false your case.

edited Nov 3, 2017 at 5:52

answered Nov 2, 2017 at 17:39

Ilya

13.5k5 gold badges41 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Src Over a year ago

@MrYoshiji You have passed Hash object to #or. Pass an ActiveRecord::Relation object instead

Src Over a year ago

@MrYoshiji Using Rails 5

MrYoshiji Over a year ago

@Src use .or(Flight.last.flight_logs.where(item_id: nil)) (you can store this scope in a variable)

Ilya Over a year ago

@MrYoshiji, I prefer my current answer (IMO it is cleaner than this long scope).

MrYoshiji Over a year ago

I agree, but I think it is good to show the different options and then argue which one is better.

Collectives™ on Stack Overflow

Active record where.not returns empty array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related