"WHERE (\"deleted_at\" IS NULL\)" doesn't persist though joins...

[jmervine]

"WHERE ("deleted_at" IS NULL)" doesn't persist though joins.

Below is the sample out of using a sub-select vs. a join along vs. a join with a deleted_at check for null.

[7] production(main)> AlarmMessage.count
=> 24110
[8] production(main)> Space.count
=> 2499
[9] production(main)> Space.with_deleted.count
=> 4479
[10] production(main)> AlarmMessage.where(space_id: Space.select(:space_id)).sql
=> "SELECT * FROM \"alarm_messages\" WHERE (\"space_id\" IN (SELECT \"space_id\" FROM \"spaces\" WHERE (\"spaces\".\"deleted_at\" IS NULL)))"
[11] production(main)> AlarmMessage.where(space_id: Space.select(:space_id)).count
=> 9157
[12] production(main)> AlarmMessage.join(:spaces, space_id: :space_id).sql
=> "SELECT * FROM \"alarm_messages\" INNER JOIN \"spaces\" ON (\"spaces\".\"space_id\" = \"alarm_messages\".\"space_id\")"
[13] production(main)> AlarmMessage.join(:spaces, space_id: :space_id).count
=> 13274
[14] production(main)> AlarmMessage.join(:spaces, space_id: :space_id).where(deleted_at: nil).sql
=> "SELECT * FROM \"alarm_messages\" INNER JOIN \"spaces\" ON (\"spaces\".\"space_id\" = \"alarm_messages\".\"space_id\") WHERE (\"deleted_at\" IS NULL)"
[15] production(main)> AlarmMessage.join(:spaces, space_id: :space_id).where(deleted_at: nil).count
=> 9157

[jmervine]

After looking though your code, I realized that this doesn't work because AlarmMessage in this context doesn't have paranoid enabled, rather, Space does. However, I'm leaving this open because I feel like the expected behavior is that paranoid would work here, at least on the surface. Perhaps there's a simple solution I'm not seeing or if nothing else, this will help others in find a solution / work around.

Cheers!

[jmervine]

As an addendum to my last comment, it appears that the reverse join does filter correctly.

[25] production(main)> Space.join(:alarm_messages, space_id: :space_id).count
=> 9157
[26] production(main)> Space.join(:alarm_messages, space_id: :space_id).sql
=> "SELECT * FROM \"spaces\" INNER JOIN \"alarm_messages\" ON (\"alarm_messages\".\"space_id\" = \"spaces\".\"space_id\") WHERE (\"spaces\".\"deleted_at\" IS NULL)"

[sdepold]

Interesting :) @jmervine as I'm not really in a situation where i develop with ruby or use sequel anymore, I wonder if you want to give it a try?

[jmervine]

I'll take a look, but the more I'm working around this issue, the more I'm thinking it's really expected behavior and anything done to try and fix this within paranoid would be really hacky and unwieldy.