Reordering `items`

[iteles]

As a person with a lot of different tasks, I would like a way of reordering the list of timers So that I can ensure my most important tasks are captured at the top so that I don't lose any important/urgent items.

I think this is the highest priority next feature to include in the MVP. I've been using the MVP for a few days now and find myself reading the whole list of tasks before starting the next one which is unsustainable (and means I barely put any tasks into it).

The annoying thing about this one is that the UI is not straightforward.

[nelsonic]

@iteles did you see: https://github.com/dwyl/app/issues/283 ? 💭

[nelsonic]

We're going to SPIKE re-ordering today: https://github.com/dwyl/learn-alpine.js/issues/4 🤞

[iteles]

Just putting in another note on this as it's a feature I miss Every day when I'm searching through my list for a task I need to do next and prevents me from using the application properly (i.e. downloading my whole life onto it) because I know if I do that I won't be able to find anything again 😬

[nelsonic]

@iteles I agree that re-ordering is a massively important feature. 👌 Please sit down and do a paper sketch for how you want this to work. ✍️ T10min Take a photo of your sketch and share it on this issue and I will convert it to Figma and flesh-out the steps we need to build it in the Flutter App then we can prioritise the work and get it done ASAP.

For the record: At present you can "find things" by doing command+F.

I have hundreds of items in the "crappy" MVP: https://mvp.fly.dev/stats

because I am obsessed with solving this problem. I don't want to use any existing [closed source] "Todo List" App form the App Store because they are woefully incomplete and I don't want to waste my time on them.

The only excuse for not using the MVP is that you haven't understood how important dogfooding is to the success of the company. 😢

Again, Iagree that this feature is important. 1️⃣ 🔥 Please help us get the ball rolling on building it by taking the next step: researching/defining the desirable UI/UX for the feature.

[iteles]

It is precisely because I AM dogfooding the app that I opened this issue and am noting that I still feel the pain.

For a user like me, not having this feature renders the app unusable. If I have more than 30 items in my list I am completely overwhelmed and spend more time looking for what to do next than actually getting anything done. Given I have MAYBE 2 hours of computer time available to me a day at the moment (and that that is time interrupted constantly), this is useless.

I basically only add things into the app that I have to get done in the immediate future rather than actually using it as intended.

I will try to sketch this asap.

[nelsonic]

Cool. looking forward to seeing the sketch. ✍️ Meanwhile "user" ... dwyl.github.io/book/auth/07-notes-on-naming 👤

[iteles]

A couple of considerations here:

Drag & drop

This is the most familiar UX and by far the most desirable.

The problem with designing this particular functionality as someone who does not also know how to code it is that I run the risk of making it overly complex for the MVP.

There are certain parts that I know are not difficult, but the actual dragging and showing the user where the item is about to be dropped before it is dropped are where I think the highest potential to complicate occur.

Would it make sense for me to suggest the UX up until this point (how a user knows something is draggable, icons and interaction until the item is 'picked up') and then leave it to you guys to tell me what is possible quickly/intelligently in terms of how the user is shown where the item is being moved to (things like other items moving gracefully down, using just a dark line to show between which two items it'll be, whether the item you're moving 'pops' and can physically/visually be dragged) in terms of the code? Like the types of Drag n Drop in this article.

Accessibility

Of course the WCAG guidelines make it clear that drag n drop is not particularly accessible: https://www.w3.org/WAI/WCAG22/Understanding/dragging-movements In the long run, we would need a simple keyboard-operated solution (not difficult, this is as 'easy' as an arrow up and arrow down button on each item) but this would ideally be hidden from users who don't require it or who have JS capabilities and can use the drag n drop functionality.

@nelsonic As the lead Everything on this project, I'll defer to your knowledge on whether we have to progressively enhance this from the beginning or whether we can build in drag n drop and later retrofit the keyboard-focused solution.

[nelsonic]

@iteles as the person trying to build the bridge from the other end I would very much appreciate that you take T10mins to paper-sketch this. Re-ordering without drag-and-drop is very tedious but still possible using numeric indexs. Could you just please sketch what you want to see and we can take it from there. 🙏

[iteles]

Reordering items

This is a pretty familiar user pattern and I don't think we should invent too much because we don’t want this functionality to get in the way of the app itself. It should just work as expected without people using the app having to even think about it.

Mouse-operated drag-n-drop

1. Icon & positioning

The most common use pattern is for 6 dots (2 columns of 3) to appear on the left hand side of an item. These only appear when mousing over the item:

A. Icon

Although the six dots is the most commonly used, I don’t think this is particularly descriptive.

I would suggest we go with the clearer (to me) up and down arrow icon for testing:

B. Positioning

This should definitely be placed to the left of the item because this is where people are expecting it to be and because it is out of the way of the main functionality of the app.

C. Mouse-over item or permanent position

I would prefer, to reduce clutter in the app, if it only appeared when the item was moused-over (any portion of the item). The icon should remain visible whenever the mouse is anywhere on the item area.

2. Grabbing the item

Once the icon is clicked and held, the item ‘pops’, meaning it is shown differently to the others so that the person understands that the item is ready to be moved. This can be achieved with a simple drop shadow to create a ‘popped’ effect and making the move icon bolder and a different, colour-scheme-congruent colour for the icon to allow people to know that this is the action being taken.

3. Moving the item

There are two very important points here:

• That the item moves fluidly
• That the person understands exactly where they are dropping the item and that this is easy to see, preventing having to drag and drop the same item multiple times

The rest - i.e. the way that the other items move around the one that is being dragged around - is something I’m happy to have guided by the technology at this point. The only requirement is that it flows well visually and is not a jarring interaction (like a full screen refresh on drop of the item being dragged).

For the person to know where the item is being move to, my preferred interaction is a horizontal line, spanning the width of the screen that appears between items and shows the person where their dragged item will drop. This line should be thick, span the width of the screen and be in a different, vibrant, colour-scheme-congruent colour (same as the icon).

4. Dropping the item

Once the item is dropped, the ideal action of automatically having the items re-ordered would ideally be fluid rather than jarring but I’m not sure how to explain this just yet! Especially because I’m not sure what’s possible in terms of graceful degradation without JS. If we use a library or accepted functions, this may be provided.

I need a starting point to iterate from!

Mobile

I don't think there's any way around the 'standard' drag-n-drop functionality on mobile - there aren't many options given there is a clear expectation of how it will work within a mobile app.

Whilst a tap on mobile opens edit mode, a long press makes the item pop.

This long press is anywhere on the item. If it’s easier, we can exclude areas that have specific actions like a checkbox and a timer button, but not necessarily a requirement.

It would be cool if we could maintain the same horizontal coloured line to show people where they will be dropping the item.

[nelsonic]

Quite a lot there to digest. ⏳ Let's review this on Monday morning. 👌

[iteles]

It's complex to explain but simple when seen by the person using the app.

Let's 👌🏻

[nelsonic]

Re-ordering items requires lists:

The important column in the list_items table for enabling re-ordering is index In this simple example, the person (with person_id: 3) has a list named "personal" for their personal items.

In the simplest scenario: the person wants to re-order the items on their personal list When they created the list they put "tidy kitchen" (item_id: 123 at index: 3) after "make lunch" (item_id: 124 index:2). This works if the kitchen is already tidy before they arrive in it. But they just got back from shopping and realise that the kitchen is a mess. 🙃 So they have to "tidy kitchen" (item_id: 123) before they can "make lunch" (item_id: 124).

From perspective of getting this data from the client to the server this is just sending the updated list_items:

[
  { "item_id": 456, "list_id": 1, "index": 1},
  { "item_id": 123, "list_id": 1, "index": 2},
  { "item_id": 124, "list_id": 1, "index": 3},
  { "item_id": 862, "list_id": 1, "index": 4},
  { "item_id": 345, "list_id": 1, "index": 5}
]

This is enough for us to make the update in the backend and broadcast it to any other connected clients/devices.

@LuchoTurtle please let me know if this answers your question from standup. 🙏 If not, please attempt to articulate what is unclear. 📝 (comment in the issue 💬)

[LuchoTurtle]

Thank you for replying but this does not answer my question. I already understand that we'll have an index column that will determine the position of the item within the displayed list to the person.

I understand that the index must be updated but your response doesn't encompass scenarios where an item is "drag n dropped" from index 0 to index 9, for example and how we are changing that in the database.

But it doesn't need to explain it, given that we've already discussed this prior in https://github.com/dwyl/learn-alpine.js/issues/4#issuecomment-1282194749. Implementing a simple algorithm (for now) will do the job, a la: https://betterprogramming.pub/the-best-way-to-update-a-drag-and-drop-sorting-list-through-database-schemas-31bed7371cd0.

My question was regarding how lists were even relevant to the problem of re-ordering that I was trying to do in Phoenix for the MVP. They really aren't for the question of how we are re-ordering the items in the database.

But I understand that It makes more sense to redirect these efforts to Flutter, as you've stated. However, if we want to do so, we can't because we still need to incorporate items and API connection to https://github.com/dwyl/app.

On posteriority, since we don't have the concept of lists introduced in our API, we would tackle that next.

[nelsonic]

Without lists and list_items we are forced to add the index column to the items schema.

This is the simplest (naive) way of handling re-ordering. It will work but it won't preserve the history of the list_items order. And sadly, this structure won't let papertrail maintain the history either ... (with this schema we can't wave a magic wand and have papertrail do the work for us ...) to perfectly preserve the history, we need something a little more advanced.

I've not seen this done properly in any examples/tutorials before so it needs some thought to do it as simple as possible.

[nelsonic]

⚠️ Warning: This comment shows creative thought process for solving this technical challenge from "First Principals" ... read to the end to understand how we're doing it. 💭

@LuchoTurtle Thanks for sharing the medium post: "The Best Way to Update a Drag-and-drop Sorting List Through Database Schemas" https://betterprogramming.pub/the-best-way-to-update-a-drag-and-drop-sorting-list-through-database-schemas-31bed7371cd0 It's a good read but still a naive implementation that does not preserve history. (Both @omgzui proposed schemes rely on having PRIMARY KEY (id) i.e. no history...)

But ... "Why do we want to preserve history?"

It may not be apparent in a simple "single-player" example; why would a single person care about the history of a list order when they are the only one looking at it? They don't care! Unless they want to undo a change that is. ctrl+Z 🙏 The moment they want something a little more advanced like the ability to undo, @omgzui approach fails. Also the query syntax for retrieving items form a table that has prevId, siblingId and position is needlessly complex. so, no. it's not "The Best Way to Update a Drag-and-drop Sorting List Through Database Schemas".

What is needed is an append-only log: https://github.com/dwyl/phoenix-ecto-append-only-log-example

When the items at index:2 and index:3 are re-ordered we need to insert two new records into the list_items table:

This also makes the data sent over the wire a lot more efficient than sending the whole list each time:

[
  { "item_id": 123, "list_id": 1, "index": 2},
  { "item_id": 124, "list_id": 1, "index": 3}
]

When an update is made we never have to "lock" the DB. Everything is append-only and we just have to write the appropriate query to extract the latest ordering of the items. Using a SELECT DISTINCT we can ignore the previous entries for the item_id that have been re-ordered. And we don't need the concept of prev or sibling.

Butttt.... what if the update is more advanced than this trivial two-item position swap example?

Well, that's when we need to get a little creative. 💡 😉

Let's focus on just the list_items table and ignore the rest of the diagram, we know that it's a relational DB. 👌

if we use a float for the index (instead of int) then we can do a 0.1 increment to index when a re-order is performed we can avoid large updates:

Hold on, this update to using float for index also changed the first digit in the number ... 💭 Yes, in order for this approach to work, the front-end needs to reference the previous item in the list and add 0.1 to it so that the ordering still works mathematically.

Now when we do a SELECT DISTINCT and ORDER BY item_id we get:

But wait, what happens when we need to move a different item into "position 2" in the list? 🤷‍♂️

Well, then we need to get creative again. Instead of referencing the index of the previous item in the list, we need to reference the next item in the list and decrement from it.

Using Postgres real Numeric Type we get 6 decimal digits precision see: https://www.postgresql.org/docs/current/datatype-numeric.html#

This means our index values go from 1, 2, 3, etc. to: 1.000000 2.000000, 3.000000 etc.

What this enables is the following:

Which when we SELECT DISTINCT and ORDER BY item_id we get:

Yes this implies that there is an upper-limit on the number of times that an item can be re-ordered in a list But that limit is 1 Million Re-ordering Operations per position. How soon will that be exhausted? 💭

Moving "wash dishes" (item_id: 345 and index: 5.000000 in the example above) to the top of the list 🔝 would result in the following data sent from the client to the server:

{ "item_id": 345, "list_id": 1, "index": 0.000009}

In the DB it would look like this:

and again, SELECT DISTINCT and ORDER BY item_id we get:

if the person decides that a brand new item needs to be on the top of their list e.g: "switch on meal prep playlist" (item_id: 1969 and index: 6.000000 when added to the list_items table)

The op (data) is:

{ "item_id": 1969, "list_id": 1, "index": 0.000008}

SELECT DISTINCT and ORDER BY item_id:

The recently added item at the top of the list.

But Does it Scale?

The typical list in our App is going to have a handful of items at any given time. A "Power user" might end up with a list with a few thousand items if they try really hard.

As noted above, the limit of using a real (Numeric Type with "only" 6 decimal digits precision) means that each position in the a list can only have 1 Million drag-and-drop to reorder operations per position. I've been trying to think of a scenario where this would be insufficient and I'm not thinking of one, in medium-sized team ...

But in the event that a Mega Co. uses our App and has say 100k people collaborating on a single MEGA list it would only take each person moving an item to the top of the list 10 times to exhaust the 1 Million ops. In that event we could ALTER the index to be double precision which uses 8 bytes and gives 15 decimal digits precision i.e. 1 Quadrillion reorder operations.

Given that the list_items table will not contain any PII none of the values need to be encrypted. We can easily keep an eye on the number of reorder ops that occur and determine if this ALTER will ever be needed.

tl;dr

We'll use a real (float in Ecto) for and name the column pos for "position" in the list. The word index is "reserved" in Database-land so we don't want to confuse anyone. That also reduced the number of bytes/characters sent down the wire to just:

{ "item_id": 1969, "list_id": 1, "pos": 0.000008}

This maintains the full history of the reordering operations and the person using the App can easily undo any re-ordering performed by accident.

@LuchoTurtle as always, LMK if this answers your question. ❓

[LuchoTurtle]

It does, thank you. However, let me raise a few concerns that may be helpful for people seeing this and for ourselves:

Cost of queries

By having the items table as append-only, you've stated we're forced to use DISTINCT (a known costly clause) to query the data and remove the rows that are "history". However, I'm here wondering how this SQL expression will turn out to be and I don't see a way how DISTINCT will actually be used to get the query results you want.

If anything, you'll have to use OVER(PARTITION BY...), which is a mildly slower version of GROUP BY.

Check http://sqlfiddle.com/#!18/51660/15

(I feel like this fiddle is ephemeral so people in the future might not see this).

I've loaded the list_items data you've shown in your pictures.

The query you actually want is something like this:

select item_id, list_id, position, inserted_at
from (
  select item_id, list_id, position,
  ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY inserted_at ASC) AS rn,
  inserted_at
  from list_items
) i
where rn = 1
order by position asc

Which yields the wanted result.

As you can see, DISTINCT is not used. I don't see how it can actually be used to yield the query results we actually want. Then again, you can write many SQL query statements to get the same result, so I might be wrong there.

No primary keys

By having everything in the same table as "append-only", you can't reap the benefits of having primary keys. In fact, not having primary keys is defo not recommended and should not be something we undertake lightly.

Normalization

Would normalization make sense here? If we had the history of the changes in a different table and have list_items updated with the current position (we can do this with the use of SPs), querying our data should be much faster (since we don't have to use PARTITION BY clauses).

The trade-off would probably be the duration of the CTRL-Z operation, since we have to join the two tables (history and list_items to get the last action made for a specific item_id. The other trade-off is the complexity of adding new items/updating the position since we had to do operations on two tables.

[nelsonic]

Thanks for confirming that my explanation adds clarity to the technical implementation. You are correct, the DISTINCT keyword/function is probably not needed if GROUP BY works. as per: https://stackoverflow.com/a/5391642/1148249

Not concerned about query performance. There are many optimisations and strategies we can apply that will eliminate any query latency.

For clarity: the list_items table will absolutely have an id field that will be a Primary Key we just won't use it for very much in practice.

The list_items table is the "history" table. It's Normalized as it doesn't contain any duplicate data. Each row will be unique by definition. If anything, we will use a Materialized Views for eliminating the query delay completely. See: https://www.timescale.com/blog/how-postgresql-views-and-materialized-views-work-and-how-they-influenced-timescaledb-continuous-aggregates/

I expect CTRL+Z to be used infrequently in our App. But I expect "replay" to be used a lot. In fact I consider it to be "secret sauce" that other "productivity" apps will copy.

[nelsonic]

quick update: while keeping most (90%+) of Lucho's code https://github.com/dwyl/mvp/pull/345 I have updated the underlying data structure to use lists and list_items as per #356 The drag and drop to reorder items is working, but sadly only in an "upward" direction. i.e. it doesn't work if you drag an item to a lower position on the list. This is best illustrated with the following GIF:

I'm studying the JS code to see if we can extract more relevant information on the position of the item such that moving it to anywhere can be tracked.

[nelsonic]

After a bit of head-scratching, 🤔 I think I've got an efficient way of doing this. 🤞 As mentioned on Standup today, 💬 I'm going to read the full list of items from the DOM 📱 and then simply get the position of the item_id in that list on the server. 🧑‍💻 💭 This will give us the exact position and we know if it went up or down in the list.

[nelsonic]

Ok. I've implemented the "send list of items from client" solution and it works:

https://github.com/dwyl/mvp/assets/194400/80ec4fde-86d4-4b88-bce3-8498be1c0bbb

But if you pay close attention to the end of the video: after a few moves the position no longer works! 😢 It's basically a bug in my implementation.

So I'm going to re-do this in a more robust way that will be:

a) Works every time even with multiple "players" moving items simultaneously. b) Faster when loading the list. c) Less confusing from a query perspective. d) "Shardable" https://en.wikipedia.org/wiki/Shard_(database_architecture)

Going to require a bit more work but will 100% be worth it.

[nelsonic]

Here's what I'm thinking: The "frontend" is already sending the full list of item.ids with the updateIndexs event: https://github.com/dwyl/mvp/blob/29a1c48233877a776d60ae882a9087b8a44796b9/assets/js/app.js#L36-L47

e.g: "1 2 3 42 75 86 90"

So, why not just use this as the list ordering and skip the whole move_item/3 call completely. This is similar to the idea that @SimonLab was proposing in https://github.com/dwyl/mvp/pull/165 Specifically: https://github.com/dwyl/mvp/blob/c11fb5c5fc1985a143cbfbbe73491a0cf338ed56/lib/app/item.ex#L12

However, instead of item_lists, we'll have list_items and instead of the position we will just have an sequence or seq this is the Math term for the order in a list: https://en.wikipedia.org/wiki/Sequence

So ... I'm going to do a quick refactor. And by "quick" I of course mean see you in November ... ⏳

[nelsonic]

Managed to rework the add_list_item/3 function and get_list_items/1 to be significantly smaller. ✂️ But have a bunch of failing tests in other functions because I simplified the schema ... ♻️ Will work on fixing those failing tests on the train tonight and should have this working soon. 🧑‍💻 🚂

[nelsonic]

Progress: list_items.seq contains the sequence of item ids:

And the corresponding "WHERE IN query works for retrieving items_with_timers:

SELECT i.id, i.text, i.status, i.person_id, i.updated_at,
  t.start, t.stop, t.id as timer_id
FROM items i
FULL JOIN timers AS t ON t.item_id = i.id
WHERE i.id IN (5,4,3,2,1)
AND i.status IS NOT NULL
AND i.text IS NOT NULL
ORDER BY timer_id ASC;

"just" need to update (significantly simplify) the move_item/3 function and we'll be done! 🤞

[nelsonic]

Had this error a bunch:

 ** (DBConnection.EncodeError) Postgrex expected an integer in -9223372036854775808..9223372036854775807, 
got ["5", "4", "3", "2", "1"]. 
Please make sure the value you are passing 
matches the definition in your table or in your query 
or convert the value accordingly.

I've tried parsing the String to BigInt e.g: regexp_split_to_array(li.seq, ',')::bigint[]

SELECT regexp_split_to_array(li.seq, ',')::bigint[]
FROM list_items li
WHERE li.list_id = 1
ORDER BY li.inserted_at DESC
LIMIT 1

via: https://stackoverflow.com/questions/45304552/converting-comma-separated-string-to-integer-array-in-postgres

But I feel like I'm hitting my head against a brick wall building something that won't be used in the future ... We don't want to have BigInt ids ... we want to use Offline-first cid #410 So that our App works Offline and then syncs when back online.

Before: https://github.com/dwyl/mvp/blob/c5521fbf9e8bc65726eaca8c137c41c4e0b0f29c/lib/app/list_item.ex#L126-L179

Note: I created this complexity in my vain attempt to pursue the "positional" sorting. It was needlessly complex! 🤦‍♂️

After:

https://github.com/dwyl/mvp/blob/24cad7d99300fce0105ac192022d8d529bb303cf/lib/app/list_items.ex#L22-L38

[nelsonic]

Reordering working with cid: ✅

https://github.com/dwyl/mvp/assets/194400/d8297734-9be6-4b18-8783-8ddbc5df5aae

[panoramix360]

Nice stuff!!!

[iteles]

This looks great @nelsonic, I'm excited for it!

[nelsonic]

Docs hero screenshot:

[nelsonic]

Documented in: https://dwyl.github.io/book/mvp/18-reordering.html

[nelsonic]

@iteles please test: https://mvp.fly.dev/ 🙏

[iteles]

This is slightly different to the OP from an aesthetic and UX perspective but has been working well for the last couple of weeks and makes the app so much more usable 🙌

As my lists grow (they were wiped temporarily due to https://github.com/dwyl/mvp/issues/417) I'm sure I will be able to provide more feedback for the next iteration 👍

[panoramix360]

Really nice guys that you managed to add reordering :D

The solution is growing!

[nelsonic]

@iteles as always, you have the power to make any changes you would like to see. 👩‍💻👌