Gap fill in priority column + keep order

Question

is there any good algorithm that will "fill" gaps in priority (or any other) column of table? e.g.

example

I have a table which structure looks like this:

id | text | subcategory | priority

and it is filled for example like this

1 | books    | NULL | 1
2 | dvds     | NULL | 2
5 | action   | 2    | 1
8 | romantic | 2    | 2
9 | fantasy  | 1    | 1
4 | sci-fi   | 1    | 2
6 | comics   | 1    | 3

In my design there is a option to change subcategory, if that happens priority is set to "new" priority and its value is highest in the subcategory it is currently in.
e.g change "action (id:5)" subcategory to "books (id:1)", its priority would be 4, that is ok, but now "romantic (id:8)" row has priority 2, and it is one and only in subcategory dvds (id:2).

1 | books    | NULL | 1
2 | dvds     | NULL | 2
8 | romantic | 2    | 2
9 | fantasy  | 1    | 1
4 | sci-fi   | 1    | 2
6 | comics   | 1    | 3
5 | action   | 1    | 4

-> I change fantasy (id:9) subcategory to dvds (id:2) and my priority is going to be 3.

1 | books    | NULL | 1
2 | dvds     | NULL | 2
8 | romantic | 2    | 2
9 | fantasy  | 2    | 3
4 | sci-fi   | 1    | 2
6 | comics   | 1    | 3
5 | action   | 1    | 4

It is alrgiht, but I need a function that will re-order everything by itself so I dont have to manualy change values of priority column. So priority column starts with 1.

Gaps start in row with id 8, 4. Furthermore change comics category and then change it back, its going to be all messy there will be right order but it wont look as nice as I expect (for administration purposes).

Any ideas? Pseudocode or logic would be nice.

edit - solution: pseudocode + logic

Since we know the count of rows in category and we can make a select that is going to be ordered by priority so we can just assign right number to each "new" priority.

e.g:
ordered select returns priorities as follows: 1, 4, 5, 9, 10
count(select) = 5
so "new" priorities must be as follows : 1, 2, 3, 4, 5. Just assign in foreach loop new key to value.

since its in codeigniter:
$this->category_model->getPriorities("2") gets you all priorities in order (ASC) of one subcategory in my case 2.

public function prioritize(){
    $p = $this->category_model->getPriorities("2");
        for ($i = 1; $i < count($p)+1; $i++) { 
            echo "new[".$i."]->id[".$p[$i-1]->id."]->old_value[".$p[$i-1]->priority."]<br>";    
        }
}

output:

new[1]->id[9]->old_value[1]
new[2]->id[13]->old_value[3]
new[3]->id[14]->old_value[5]
new[4]->id[15]->old_value[8]
new[5]->id[11]->old_value[10]

Answer 1

UPDATE table as tb1, table as tb2 SET tb2.priority = tb2.priority - 1 WHERE tb2.priority > tb1.priority AND tb1.text = 'action' AND tb2.subcategory = tb1.subcategory;

This should solve the gap, every item with higher id, should drop it by 1

i.e. you have in this subcategory:

| priority |
|   1      |
|   2      |
|   3      |
|   4      |

if you move the item with priority=2

3 and 4 will move as 2 and 3

P.S.: This is the solution only for the gap, since the move issue is already solved in the other answer. It should be done before the movement, because the subcategory will change after it, and will not met the WHERE clause (the items from the old subcategory)

---

About the reordering the random gap:

Let's say we have priorities:

5, 10, 11, 12, 18, 20

which we want to make as:

1, 2, 3, 4, 5, 6

So the maximum number here is 20, which needs to be marked as 6

When it's done, the maximum number will be 18, which should be 5.

So everytime you use UPDATE ... SET ... WHERE priority = MAX(priority) it will get the maximum priority.

Here's my simple test:

<?php
for ($i = 6; $i>=1; $i--) {
    echo "UPDATE table SET priority = $i WHERE priority = MAX(priority) AND subcategory = X;" . "<br/>";
}
?>

which produces:

UPDATE table SET priority = 6 WHERE priority = MAX(priority) AND subcategory = X; // 20 becomes 6
UPDATE table SET priority = 5 WHERE priority = MAX(priority) AND subcategory = X; // 18 becomes 5
UPDATE table SET priority = 4 WHERE priority = MAX(priority) AND subcategory = X; // 12 becomes 4
UPDATE table SET priority = 3 WHERE priority = MAX(priority) AND subcategory = X; // 11 becomes 3
UPDATE table SET priority = 2 WHERE priority = MAX(priority) AND subcategory = X; // 10 becomes 2
UPDATE table SET priority = 1 WHERE priority = MAX(priority) AND subcategory = X; // 5 becomes 1

So you need to make a SELECT where you are selecting the subcategory, to put it instead of "X", then you need to count the rows for the while loop:

for ($i = $count_rows; $>=1; $i++) { ...

Ofcourse, you need to put the statement in the right query function, instead of echoing it, but it was for a test purpose.

Answer 2

SELECT @NewPriority := MAX(priority)+1 FROM table WHERE category='$NewCategory';
UPDATE table SET category='$NewCategory', prioroty = @NewPriority WHERE id='$Id';