Say I have a list of items, all generated from a database. For the sake of this example, let's call them:
item1
item2
item3
...
itemN
Say I want to allow someone to drag any of these items and place them in the order they want (i.e. they can take item10 and place it between item2 and item3, so that it now shows up after item2).
This means that I need to update the ordering of all items that are after where the dragged item was placed. What is the most efficient way of doing this? Is there a trick or am I just updating everything after?
EDIT:
Would a node-approach be faster?\ By node, I mean each item is linked to the next item. This way, you only need to update three nodes' children and then everything else would properly work. So in my example above, before drag I had :
item2.child = item3, itemN-1.child = itemN, itemN.child = null.
After drag, I would need to update:
item2.child = itemN, itemN.child = item3, itemN-1.child = null
Is this a better implementation? Doing this, I can't figure out a way to then display a list of queries from the db properly ...
Well if your talking a UI way here is:
Say we have 5 elements names each element has a attr called "order"
<ob1 order="1" name="1">
<ob1 order="2" name="2">
<ob1 order="3" name="3">
<ob1 order="4" name="4">
<ob1 order="5" name="5">
Say we want to put the one with the name of "5" into first place.
It becomes:
<ob1 order="1" name="5">
<ob1 order="2" name="1">
<ob1 order="3" name="2">
<ob1 order="4" name="3">
<ob1 order="5" name="4">
When you drag name="5" to the top the order gets set to 1 then the rest get 1 added to them.
Alternatively:
Say we have 5 elements names each element has a attr called "order"
<ob1 order="1" name="1">
<ob1 order="2" name="2">
<ob1 order="3" name="3">
<ob1 order="4" name="4">
<ob1 order="5" name="5">
When you move name="5" to the top it gets the next elements sort order then subtracts 1 from it so:
It becomes:
<ob1 order="0" name="5">
<ob1 order="1" name="1">
<ob1 order="2" name="2">
<ob1 order="3" name="3">
<ob1 order="4" name="4">
Applying the same type again lets move 4 to the top:
<ob1 order="-1" name="4">
<ob1 order="0" name="5">
<ob1 order="1" name="1">
<ob1 order="2" name="2">
<ob1 order="3" name="3">
Sorry for a wall of text.
The fastest way of moving them around the database: There isnt one. Handle the sorting in your code then apply the sorting to the database.
Since, you're interested in actual logic here is the idea that I have implemented few months ago:
id => order. Most optimal way.id field)POST and the fetch data calculate the delta, that is, what has and what has not changed. This can greatly reduce number of queries to run.UPDATE queries as necessaryConclusion: I can't think of an optimal way to do it, mainly because of the nature of SQL's UPDATE query