SPIKE: Drag-and-drop reordering of items in a list

[nelsonic]

As outlined in dwyl/app#283 (comment) the next feature on our roadmap is prioritisation of items.
We will achieve this visually by dragging and dropping the item to order them in terms of the priority.

From quick searching, it looks like we can pull off basic drag-and-drop using Alpine.js: 🤞
https://codeblog.trovster.com/2020/05/alpinejs-drag-and-drop/ 📝
https://www.youtube.com/watch?v=zJ4NQaiYxKs 📺
https://codepen.io/trovster/pen/oNjGGMq 👨‍💻

Screen.Recording.2022-09-30.at.10.36.28.mov

Our objective is to SPIKE this ONE feature in a standalone way, see: dwyl/technology-stack#96
Essentially we just want to test that it's _possible to:

a. Have a bunch of items on a page with the following fields:

• text
• id
• index

b. Drag an item to a different position should refresh the ordering i.e. index of the adjacent items
c. If the item from the bottom is moved to the top it will now have index=1

Note: we're not starting our index from 0 as this is not intuitive to non-technical people.

d. All the other items will have their index updated. So we need to get the item.id for each item on the page and update it's new index.
e. Sync this update to another connected client e.g. using LiveView.

Todo

• Create a new Phoenix App in this repo. call it "app" so that it's easy to copy-paste working code to the MVP later.
• In that Phoenix App, add the usual setup: Tailwind + Alpine see: mvp/BUILDIT.md#1-create-a-new-phoenix-app
• Create an items schema but without people or any other unnecessary data/fields. just:
  • id
  • text
  • index
• Create a LiveView page where we can build this experiement!
• Update templates to use Tailwind classes (see SPIKE: Drag-and-drop reordering of items in a list #4 (comment))
• Try to implement the most basic drag-and-drop re-ordering and persistence+sync implementation possible.

One More Thing ...

• Must work flawlessly on Mobile. 📱

Technical Implementation is 100% Flexible at this point!

If you find a more efficient way of implementing this please share!
Right now we are 100% in the research & development phase.

Good luck! 🍀

[LuchoTurtle]

The way we store the index within the database raises a few performance concerns about the atomic way of updating rows within the table. Since we're using the "drag n' drop" approach, the amount of rows we ought to change every time we drag and drop can be considerably high.

Luckily, Jira already has this problem and they fix it in a rather interesting way - you can find it here. They rank each item through strings and use a minimising string length algo to generate the minimum length possible string when changing ranks, changing only two rows at a time.

Basically, the "rank" (what we would call index) is a string value that allows a ton of breathing room in between ranked items.

[nelsonic]

Agreed. The number of updates for each drag-and-drop event can potentially be the whole list.
But luckily we don't need to worry about that for the next year (at least!)
because a decent capacity Postgres instance/cluster will handle 100k people using it without breaking a sweat.

If each person has average 1k items spread over 10 lists i.e. 100 items per list (though I think is quite a lot !) Then 100k people would have 100,000 x 1000 = 100,000,000 (100 Million) items in the DB.
If the average size of an item is 1kb then it's 100 gigabytes for the whole DB.
It's not unheard of to have a 50 terabyte PostgreSQL database:
https://www.adyen.com/blog/updating-a-50-terabyte-postgresql-database
And there instances using Peta-Bytes ... but for that we will resort to citusdata.com ...

If we notice a significant performance bottleneck in this, we can target it for optimisation. 🔍

Thank you for sharing the link to the StackOverflow https://stackoverflow.com/questions/9536262/best-representation-of-an-ordered-list-in-a-database/49956113#49956113 ... as noted, I don't expect that our DB will suffer too much from updating the rows for a list each time the ordering/priority is updated. This is a totally routine task in Postgres and Ecto. Even in a DB with 100 Million rows this will still happen in less than 50 ms because the update query will just be:
{item_id=1, index=1}, {item_id=7, index=2}, {item_id=4, index=3}, etc. (pseudocode)

This is a very fast update query even if there are hundreds of items in the list
and should not affect the performance/UX of our App for the foreseeable future. ⏳

Going to avoid using Strings for the "rank" (index) until we are forced to use them.
Using an int for the index is more intuitive IMO.
if we reach the JIRA scale (complexity) it means we have more than enough cashflow 💰
to hire a PostgreSQL expert to do these kinds of micro-optimisations. 👨‍🏫

For now we are targeting having 10k paying customers. 🤞
Using an int for the index is the appropriate technical implementation until we need to scale beyond that. 💭

[SimonLab]

For:

Create an items schema but without people or any other unnecessary data/fields. just:
Create a LiveView page where we can build this experiement!

task describe in #4 (comment)
I'm thinking of using mix phx.gen.live
This should speed up the setup for creating items (create schema, routes, live templates). I should be able then to add Alpine.js functions to create the drag and drop

running mix phx.gen.live Tasks Item items text:string index:integer (I'm not sure I like the name index, maybe priority is better to describe the order of an item? We can change that later in the mvp if necessary):

Because Tailwind is not included by default on Phoenix yet, the styles will need to be updated for the genereated liveview templates:

[SimonLab]

My latest commit makes shure the drag and drop events are saved (items' indexes udpated) and all clients are notify to the new list order: b3b4db2

I need to update the Readme and the tests but you can already test the drag and drop with mix phx.server

[SimonLab]

I spent a bit mote time trying to implement a UI to show which element is moved on all clients:

drag-and-drop.webm

When an element is grabbed it is highlighted and moved on all the connected clients.
Only when the item is released that we update the index for all the items.
I think it start to look nice. I still need to write the Readme on how this is done and it would be great to deploy it on Fly and test the feature with a long list of items with multiple connected clients

[nelsonic]

Siiiiiiiick! 🤩 @SimonLab 🙇

[SimonLab]

I'm keen to try to create lists for items and to test drag and drop of items between lists. Now that I have a better idea how Phoenix liveview and Alpine.js can work together to make the drag-and-drop visible on all clients I don't think it should be too complicated to implement.

And the last step would be to apply the drag and drop to lists (ie reorder how the display of the lists).
I'll have more time tomorrow and give this a go

[SimonLab]

I'm following the instruction again on the drag-and-drop.md file to see if I need to add more details on some section. I'll add some screenshot on this issue that I can reference on the file.

Adding Alpine.js to LiveView, see https://hexdocs.pm/phoenix_live_view/js-interop.html

Create items:

[nelsonic]

This is working well on #5 ✅

[nelsonic]

Deployed to: https://alpine-demo.fly.dev/items

alpine-drag-and-drop.mov

Working well. ✅

[nelsonic]

@SimonLab please note: one of the original requirements in the OP ⬆️
Was for this to work "flawlessly" on mobile:

I've attempted to test it on my iPhone: https://alpine-demo.fly.dev/items
And cannot grab/drag any of the items. 😕

Were you able to do it on your Android phone? 🤞

[SimonLab]

Unfortunately I think the drag and drop html api is not very well supported on mobile.
I'll have a better look to see if there is a solution

[nelsonic]

@SimonLab let's not invest toooo much time in this. ⏳
I just wanted to document that it wasn't working on iPhone and confirm with you if you had the same issue on Android ...
As you say, mobile is not well-supported. Not much we can do about that .. 📵
We'll address this in Flutter-land 📱 https://docs.flutter.dev/cookbook/effects/drag-a-widget