When defining scope can we make the system ignore scope of particular value

[coderhs]

Apologies if this isn't the right place for this question. Feel free to delete it and point me to the correct location, and I'll repost there.

I'm working on a todo list application where tasks are organized into buckets (e.g., "today," "later," "important"). These buckets are defined as hardcoded enum values, and there's an "archive" bucket for completed tasks older than 7 days.

Currently, when a task is moved between buckets, the application reorganizes items in both the original and the new bucket. This is causing an issue with the archive bucket, as it grows to over 5000 completed tasks for one user.

Is there a way to prevent the archive list from updating when a task is moved, as the updates feel unnecessary?

[brendon]

Hi @coderhs, great question :) Thanks for reaching out to ask.

You're right, there isn't a way for something not to be in a scope. This has come up quite a bit but I'm not really sure how to solve it at the moment the way the code is written.

I'd imagine the performance penalty won't be too great in the default scenario as I imagine it: An item from an active list gets moved to the archive list which by default (unless you specify a new position) will be at the end of the list. The reordering functions will fire on the archive list but they won't actually affect any existing members as there are none beyond the end of the list.

Are you noticing actual slowness in your queries? We do use a fairly unique method to ensure uniqueness is maintained during the transaction by inverting the positions of the affected items to negative and then back to positive with an increment. Closing the gap in the active list wouldn't take long since it's not likely to have that many members I assume :)

[coderhs]

Yes, we were placing it at the top of the list during the update. For now, we'll place it at the end and sort the list in the view.

Thank you for the detailed response.

[brendon]

No worries :) Glad you found a solution that worked. Was the database update quite slow with 5000 records?

[coderhs]

Yes, relatively. We were placing it at the top, but it runs faster when we don't.

Here are the logs from my local machine when placing it at the top:

Task Update All (338.3ms) UPDATE "tasks" SET "position" = "position" * -1 WHERE "tasks"."user_id" = $1 AND "tasks"."bucket" = $5 AND "tasks"."position" >= $3 [["user_id", 1], ["bucket", 5], ["position", 1]]
Task Update All (239.6ms) UPDATE "tasks" SET "position" = "position" * -1 + 1 WHERE "tasks"."user_id" = $1 AND "tasks"."bucket" = $5 AND "tasks"."position" <= $3 [["user_id", 1], ["bucket", 5], ["position", -1]]

Our production DB server is smaller, and we have concurrent active users. So its slow enough for users to notice.

[brendon]

Interesting. The unfortunate problem is one of the major DB vendors doesn't supported ordered updates so we're unable to shift items out of the way in a way that won't break uniqueness of the positions. If that was the case then at least there would only be one query and not too. The one query still looks like it could be slow though, so I guess your solution of appending to the end works either way.

If you have any ideas for how to do this without the uniqueness problem then let me know :)