I have a MariaDB table with an auto-incremented index, but also a "sortorder" field that controls the, well, sort order, when data is queried and displayed.

E.g.

id    title     sortorder
1     this      10
2     that      30
3     other     20
4     something 25

So far, so good. I'd like to create a function to automatically re-order these though - well, not re-order, but redo the values of the sortorder column per the existing order. The desired outcome from the above after running the function would be this:

id    title     sortorder
1     this      10
2     that      40
3     other     20
4     something 30

Is this something that can be done with an SQL statement in MariaDB (I have not found anything for that yet), or do I need to do it in my (php) application?

The logic for the new sort order values is based on the ordering by the sortorder column.

The reason for renumbering is that the sort order values are going to be manually maintained in the application, but it may be occasionally helpful to start with a clean slate. Users will be trained to "leave some room" in the values to allow for future edits.

On day one, "sortorder" will get (manually) populated with, say, 10, 20, 30, etc. Or possibly 100, 200, 300, etc. So that if they need to reorder things in the future, this will allow changing one item's sortorder value to say 25, to put it between the items with 20 and 30. Make sense?

But eventually, it's possible that the users could paint themselves into a corner, or at any rate make things confusing for themselves. It would be nice to build them a button that simply goes through the rows, and re-sets all the sortorder values, to preserve the existing row order but to make the values of sortorder be spaced evenly by intervals anew.

CodePudding user response：

This would require some subqueries to be written inside. Steps of what I did are as follows:

• Table name I used is tt. You need to change it according to your table name.
• First is to get all rows in sorted order of sortorder column.
• Second, declare a variable, say @serial_no and keep incrementing it by 1 on every selected row. This is an old school technique but I find it more readable.
• Assign new sortorder values in this new parent select query. For now, I have just multiplied it's serial number(as in rank) by 10. You can adjust accordingly.
• In your update query, inner join current copy of the table being updated with this select query and update the new sortorder column values correctly by matching them on id column.

Snippet:

update tt A 
inner join (
    select id, title, (@serial_no := @serial_no   1) as serial_no,@serial_no * 10 as `sortorder`
    from (
        select *
        from tt
        order by sortorder asc
    ) temp_derived,(SELECT @serial_no := 0) as sn    
) B
on A.id = B.id
set A.sortorder = B.sortorder

Update:

I just realised the control is completely shifted from user to DB. If you wish to update multiple rows with their new sortorder values, I wish to propose a workaround technique since I have never seen updating multiple rows with new values submitted from user in bulk(happy to learn if there exists one).

• You need to map old values with new values, say in an associative array in PHP.
• Start a DB transaction in MySQL.
• Insert all new rows in bulk.
• Delete all previous old rows in one go with IDs sent from PHP (in a prepared statement preferably with the previously mapped assoc array)
• Commit the transaction.
• Rollback ofcourse if something goes wrong.

CodePudding user response：

Update: This solution works in MySQL 8.0, but not in MariaDB, because MariaDB's support for CTE doesn't support UPDATE statements. I'll leave this solution here for readers who use MySQL, but it doesn't work for MariaDB.

mysql> select * from NoOneEverNamesTheirTableInSqlQuestions;
 ---- ----------- ----------- 
| id | title     | sortorder |
 ---- ----------- ----------- 
|  1 | this      |        10 |
|  2 | that      |        30 |
|  3 | other     |        20 |
|  4 | something |        25 |
 ---- ----------- ----------- 

mysql> with cte as (
    select id, row_number() over (order by sortorder) * 10 as new_sortorder 
    from NoOneEverNamesTheirTableInSqlQuestions
  ) 
  update NoOneEverNamesTheirTableInSqlQuestions join cte using (id) 
  set sortorder = new_sortorder;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 4  Changed: 2  Warnings: 0

mysql> select * from NoOneEverNamesTheirTableInSqlQuestions;
 ---- ----------- ----------- 
| id | title     | sortorder |
 ---- ----------- ----------- 
|  1 | this      |        10 |
|  2 | that      |        40 |
|  3 | other     |        20 |
|  4 | something |        30 |
 ---- ----------- -----------