Gap filling in the priority column + maintenance order

Question

Is there a good algorithm that "fills" the gaps in the priority (or any other) column of the table? eg.

Example

I have a table whose structure looks like this:

id | text | subcategory | priority 

and it is filled, for example, as follows

 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 project, it is possible to change the subcategory, if this happens, the priority is set to a “new” priority, and its value is the highest in the subcategory in which it is currently located.
eg change the "action (id: 5)" subcategory to "books (id: 1)", its priority will be 4, this is normal, but now the "romantic (id: 8)" line has priority 2, and this is one and only 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 the subcategory fantasy (id: 9) to dvds (id: 2), and my priority will 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 

This is alrgiht, but I need a function that will reorder everything by itself, so I don’t need to manually change the values ​​of the priority column. The priority column begins with 1.

Whitespace starts on the line with id 8, 4. Also, change the comic book category and then change it, everything will be random, there will be the correct order, but it will not look as good as I expect (for administration purposes).

Any ideas? Pseudocode or logic will be nice.

edit - solution: pseudo code + logic

Since we know the number of lines in a category, and we can make a choice that will be sorted by priority, so we can simply assign the correct number to each “new” priority.

eg:
ordered selection returns priorities as follows: 1, 4, 5, 9, 10
count (select) = 5
therefore, the "new" priorities should be as follows: 1, 2, 3, 4, 5. Just assign a value to the new foreach loop key.

since its in codeigniter:
$ this-> category_model-> getPriorities ("2") gets 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 space, each element with a higher identifier should lower 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

PS: This solution is only for breaking, since the transfer problem has already been solved in another answer. This must be done before the move, because the subcategory will change after it and will not execute the WHERE clause (elements from the old subcategory)

---

About reordering a random break:

Let's say we have priorities:

5, 10, 11, 12, 18, 20

which we want to do as:

1, 2, 3, 4, 5, 6

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

When this is done, the maximum number will be 18, which should be 5.

Therefore, every time you use UPDATE ... SET ... WHERE priority = MAX(priority) , it will get the highest priority.

Here is 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 do SELECT when you select a subcategory, instead of "X", then you need to count the lines for the while loop:

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

Of course, you need to put the operator in the correct query function, instead of repeating it, but that was for testing purposes.

Answer 2

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