in reply to [OT] MySQL recalibrating a sort-index

What do you mean by "creating code for each case"? 
SELECT 1 + COUNT(1)
INTO @sort
FROM t_tree AS t
WHERE t.f_parent_id = 2
LIMIT 1;

UPDATE t_tree AS t
SET f_sort = (@sort := @sort - 1)
WHERE T.f_parent_id = 2
ORDER BY f_sort DESC;

[download]

($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,

[download]

Replies are listed 'Best First'.
Re^2: [OT] MySQL recalibrating a sort-index
by LanX (Saint) on Feb 16, 2018 at 09:52 UTC
    > What do you mean by "creating code for each case"?

    • Re-numbering from the end covers only the insert case.
    • If you delete elements you need to renumber from the start.
    • If you move elements you need to delete and insert...

      (Move is even trickier because in which state are the elements in between? And what happens if an error occurs then?)

    • There are even two kinds of "move", moving inside the same array and moving from another array.

      (But I think this is easy to handle, just change the parent_id in the second case)

    • Finally there is the copy case, where elements must be (deeply) cloned before inserting. (But this problem exists in all models)

    Hope this is clearer without needing to produce examples.

    My approach allows to stack multiple such operations and just to renumber at the end.

    Cheers Rolf
    (addicted to the Perl Programming Language and ☆☆☆☆ :)
    Wikisyntax for the Monastery

[reply]

In Section Seekers of Perl Wisdom