I have done some research into this, and settled on 2 options, maybe someone here has a better 3rd option for me.
pretend we have a table like this
Priority | Itemnumber
100000 | Item 1
200000 | Item 2
300000 | Item 3
Now we want to move Item 3 between item 1 and 2
Option 1
This option is limited to how many times you can re-prioritize.
get the next 2 lower priority numbers of item 3 and add them together and then divide by 2 getting 150000
set priority of Item 3 to 150000
Option 2
This option takes one more query
get the next lower priority of Item 3
set the priority of Item 3 to 200000
set the priority of Item 2 to 300000
Is there a better Option 3?
Storing sort order in mySQL
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
Storing sort order in mySQL
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här
Re: Storing sort order in mySQL
To me: OPTION 2
Do you know this https://en.wikipedia.org/wiki/Tower_of_Hanoi ?
It's a similar question.
Do you know this https://en.wikipedia.org/wiki/Tower_of_Hanoi ?
It's a similar question.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Storing sort order in mySQL
Not sure about the best idea, but if the table is not too large and the overhead is not too much then option 1 with a reindexing might be "neatest" in MySQL:
Change the priority of item 3 to fall between 100000 and 200000.
Then run the SQL query(adapted from code here: http://stackoverflow.com/questions/1024 ... l-database)
Test first on a non-critical database
Change the priority of item 3 to fall between 100000 and 200000.
Then run the SQL query
Code: Select all
SET @newid=0;
UPDATE tablename SET priority_key_id=(@newid:=@newid+100000) ORDER BY priority_key_id;
Test first on a non-critical database