Apply Model scope to positioned declaration.

[bryanrite]

In particular, for soft deleted records using something like discard, where I really only want positioning to manage the records that have been kept... over time, positioning records that have been soft deleted can cause a lot of unnecessary DB updates.

It can be done while healing with Model.kept.heal_position_column!, or adding discarded_at to the scope, but then its managing a deleted and a non-deleted list, which is a bit weird.

Do we want to allow applying a scope like this? It would be left to the user to hook into the proper places (like discard or undiscard) to reposition the items correctly.

[brendon]

This has come up a few times. It adds extra complication trying to figure out when a record is going in and out of a scope we care about. I'm happy to look at a PR that implements this but it'd have to be tidy and generally applicable.

Is it just a case of pre-optimising something that isn't causing that much extra load anyway? :)

[bryanrite]

Its a real concern for our use case, consistently doing updates to 150k+ "deleted" rows, but I get your point, how and when to trigger the sorting is not straight forward or clean to abstract into a lib based on an arbitrary scope.

I've made it manageable by partitioning on the soft deleted column so they are sorted separately and the "deleted" ones are always set to the last position, so there is minimal updating.

Thanks for your time and input.

[brendon]

Thanks @bryanrite, were you finding the queries that this library produces to be quite hard on the database? I've certainly not tested things with that many records. Is that 150k records within one scope (e.g. a common parent plus deleted?). I'd imagine most scenarios where you'd want to have user controllable positioning wouldn't have so many records since the user interface for sorting wouldn't be very user friendly.

[bryanrite]

Its a scenario where the ordering is important and user-adjustable, the number of "active" records is quite small, but it is high churn caused by programmatic changes, so for each scope there were many deleted records (sometimes 1M+) and often only 10s of non-deleted records. We are required to keep the deleted records for metrics and audibility, so offloading to cold storage isn't an option as well. Each individual change, even of 1M records, was not overly time consuming, but the rate and breadth of the changes happening across all scopes was considerable walltime in our DB... not a normal scenario to optimize for in the gem after some thought.

Luckily the gem is configurable enough that additionally scoping on the discarded_at field and making sure we just set deleted records to the last position was enough to significantly limit the reordering.

I originally opened the discussion because I thought a "soft-delete" scenario would be pretty common, but like we said above, the triggers are complicated to generalize and the level of data I was working with is likely uncommon and a pre-optimization for most.

Thanks for your feedback @brendon !

[brendon]

No worries at all :) Yes scoping on discarded_at sounds like a good solution. Changing the scope automatically adds the record to the end of the list (provided you don't 'change' the position in the attributes) so I don't think you need to do any special catering for that. I'd be interested to hear if you did need to though, and why :) Might be something we can optimise.