CODEWITHSUNDEEP
arrayssqlitesorting
Simen
Simen

Reputation: 447

Reorder all rows in a SQLite table with a custom `order_index` column

I have a SQLite table persons:

+-----------+----------+-------------+
| id        | name     | order_index |
+-----------+----------+-------------+
| (primary) |          | (unique)    |
+-----------+----------+-------------+
| 0         | Paul     | 1           |
+-----------+----------+-------------+
| 1         | Sammy    | 2           |
+-----------+----------+-------------+
| 2         | Caren    | 0           |
+-----------+----------+-------------+
| 3         | Muhammed | 3           |
+-----------+----------+-------------+

I have to keep track of the order using a custom column order_index. The order_index column has to start at 0.

Let's say I want to move Muhammed to position 1. This requires me to update the order_index of Sammy and Paul too. The order_index of Caren doesn't need to change, since 1 > 0.

Is it possible to achieve this using SQLite?

Upvotes: 1

Views: 302

Answers (3)

Simen
Simen

Reputation: 447

I have created a solution (demo) based on Tim's and forpas's suggestions:

# Move person from index 4 to index 2:

# Update target index.
update persons
set order_index = NULL                                        
where order_index = 4; # FROM

# Update indexes >= TO.
update persons
set order_index = order_index + 1                                        
where order_index >= 2; # TO

# Update indexes > FROM.
update persons
set order_index = order_index - 1                                        
where order_index > 4; # FROM

# Update target.
update persons
set order_index = 2 # TO                                     
where order_index IS NULL;

# Show results.
select id, name, order_index from persons ORDER BY order_index;

I think this solution is somewhat easier to grasp than forpas's suggestion. However, are there any reasons to use forpas's suggestion instead?

Upvotes: 0

forpas
forpas

Reputation: 164099

This requirement is tricky because it involves multiple updates which depend on each other and the order of these updates is not guaranteed.

First update all the other rows that must be updated except the row of the person that you want to move:

with cte as (
  select name, order_index current_index, ? new_index
  from persons 
  where name = 'Muhammed'
) 
update persons
set order_index = order_index + 
  case 
    when (select current_index from cte) > (select new_index from cte) then 1 
    else -1 
  end
where name <> (select name from cte) 
  and order_index between 
    min((select current_index from cte), (select new_index from cte))
    and
    max((select current_index from cte), (select new_index from cte));

Then update the row of the person that you want to move:

update persons
set order_index = ?                                        
where name = 'Muhammed';

Replace ? placeholders in both queries with the new position.
See the demo.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

This is not a complete answer, because your entire data model might need to be changed, but I would handle this specific requirement with the following query:

UPDATE persons
SET order_index = (SELECT MIN(order_index) FROM persons) - 1
WHERE name = 'Muhammed';

That is, just bump Muhammed's position to one minus the current highest priority. Then, your business logic should be to always serve the person with the smallest order index (which might even go negative).

Upvotes: 0

Related Questions