如何在更改其他行时更新"sorting_order“列？

Question

在添加新的类别和更改现有的类别时，我需要为类别设置自定义顺序。我相信在这两种情况下，代码和sql查询将是相同的。在我的webshop_categories-table中，我有一个so-column (排序顺序INT)，用于确定哪些类别应该输出给查看器。

下面是一个简单的示例，说明我的类别表是什么样子(so = sorting_order)：

id  |   name   |  so
--------------------
1   |   Shoes  |  1
2   |   Hats   |  2
3   |   Bags   |  3
4   |   Coats  |  4
5   |   Rings  |  5

当添加新类别时，不需要定义排序顺序，我只需提交一个表单，并运行如下查询：

$so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
$ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);  

(这将增加一个新的类别，并将其放在末尾。在这种情况下，so将设置为6)

这是添加一个新类别的最基本的方法。效果很好。但在大多数情况下，在处理类别时，需要将其放在一个特定的顺序中来表示。

但是，通常在添加一个新类别时，人们会希望指定与现有类别相比，这个新类别应该放在哪里。当更新so-column成为我的大脑扭曲器时.

我添加一个新类别的简化形式：

    At the end
    At the top
    
    after 
  
  


query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
  } else {
    $so = $_POST['so'];
  }
  $ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);
}
?>

我已经看过第一个场景:没有指定排序顺序。但是当涉及到使用这个特性时，我需要更新受影响类别的so。如果我在鞋子和帽子之间添加一个新的类别，我将在选择列表中使用“鞋后”--这意味着这个新类别应该得到2的so-value。这意味着，对于Hats和上面的所有类别，有必要对so做一个D10。

做这件事的好方法是什么？

与此相当相同的情况是，当类别从较高的so数移至较低的类别时。需要为移动类别留出空间，并将其馀的部分向上(对其+1执行so)。当然，只有介于这两种立场之间的。

相反的，它应该是非常相似的--将一个类别从一个较低的数字移到一个更高的数字。唯一的区别是，我们现在需要缩小这两种粉末之间的差距。如果我将帽子移到after Coats - Hats将变成4，其余的将需要被撞掉一个(做一个-1到他们的so)。

在使用此特性时，我试图通过获取受影响的类别来实现这一点，并在foreach循环中更新每个类别。但我无法让它像预期的那样在所有的场景中发挥作用。

创建所有行的副本(到PHP数组中)，删除它们，创建一个新列表并进行更新，然后将它们插入回也是一个解决方案，但我认为不应该仅仅为了进行这些更改就需要.但这是我想出来的一种方法.

有什么建议吗？

Answer 1

Add新类别:

    $t_cat = 'jeans';//new category to insert
    $so_to_set = 2;// so for new category

首先更新大于或等于so的所有$so_to_set

    $query = "update categaries set so = so+1 where so >= $so_to_set";

现在用category插入新的$so_to_set

    $query = "insert into categaries(name,so) values($t_cat,$so_to_set)";

更改 so for类别:

假设我们使用categaries和so进行跟踪

    +-------------------------+
    | id  |   name    | so    |
    +-----+-----------+-------+
    | 1   |   shoes   |  1    |
    | 6   |   jeans   |  2    |
    | 2   |   shirts  |  3    |
    | 3   |   pants   |  4    |
    | 4   |   blazzers|  5    |
    | 5   |   bags    |  6    |
    +-------------------------+

    $a = 2;// so of category whose position we want to change
    $b = 5;// new so for category

    // get id of category whose position we want to change
    $temp_id = "select id from categaries where so = $a"; 
    // that would be 6

现在，在$a和$b之间更新所有这些

    if($a < $b){
        // if $a is less than $b then
        $set = "so = so - 1";
        $where = "so > $a and so <= $b";
    }else{
        $set = "so = so + 1";
        $where = "so < $a and so >= $b";
    }

    // update so's
    $query = "update categaries set $set where $where";

现在对$temp_id进行更新

    $query = "update categaries set so = $b where id = $temp_id";

这一过程的最终产出将是

    +-------------------------+
    | id  |   name    | so    |
    +-----+-----------+-------+
    | 1   |   shoes   |  1    |
    | 6   |   jeans   |  5    |
    | 2   |   shirts  |  2    |
    | 3   |   pants   |  3    |
    | 4   |   blazzers|  4    |
    | 5   |   bags    |  6    |
    +-------------------------+

Answer 2

排序应以大于一步的方式执行。为了更好的视觉表示，我更喜欢等于10的步骤。主要思想是，新的项目总是可以插入SO=N+1，通常是未使用的。比如说，你在30到40之间插入了一些东西，然后你的so序列现在看起来是这样的: 10-20-30-31-40-50。

然后，您必须以这种方式对表进行UPDATE：

SET @cnt = 0;
UPDATE table AS w
   SET w.so = @cnt := @cnt + 10
 ORDER BY w.so ASC
;

UPDATE的结果是，so场由10: 10-20-30-40-50-60的均匀分布的数字填充。现在31变成40，40变成50等等。

实际上，为了数据的一致性，最好将所有的插入/重数值封装到START TRANSACTION .. COMMIT;中。同时两个INSERTS与相同的so=N+1可以驱动问题。此外，还建议为UNIQUE字段提供so索引。

稍后，当从表中获取数据时，必须将so值除以10才能得到自然数的简单序列：

SELECT w.so / 10 AS so
     , ... 
  FROM table AS w
 ORDER BY w.so
;