数据库模式 : What's the standard way for manually sorting a table?

Question

我有一个 (Postgres) 数据库表，我想在其中添加一个手动“排序”字段。在应用程序的前端，我将有一个拖放字段，以便用户可以手动重新排序条目，然后应该发布一个 AJAX 请求来重新排序数据库中的条目，我只是想知道如何在数据库中编排它。

例如，我能想到的最明显的选择是为每个排序值 >= 新排序选项的条目增加“排序”整数，但这将是过度的(我认为，不必要的)如果这些列表超出了少数几个项目，数据库就会很沉重。

另一种选择是将“排序”列设为 BigDecimal 并为其赋值

SortValue[A] = SortValue[B] + (SortValue[C] - SortValue[B])/2

A 是我要重新排序的字段，B 是它正上方的字段，C 是它下面的字段，但这似乎是一个非常困惑的解决方案，更不用说可能受到小数位限制的限制。

我确信这是一个非常普遍的问题。有效允许对数据库表进行手动排序的标准方法是什么？

干杯...

Answer 1

假设你有这样的数据:

id | pos
---+----
 8 |  1
 3 |  2
 6 |  3
 7 |  4
 2 |  5
 1 |  6

并且您想将 2 从位置 5 移动到位置 3。

您需要做的就是:

update t set pos = pos + 1 where pos >= 3 and pos < 5

打洞:

id | pos
---+----
 8 |  1
 3 |  2
   |
 6 |  4
 7 |  5
 2 |  5
 1 |  6

然后是:

update t set pos = 3 where id = 2

填坑:

id | pos
---+----
 8 |  1
 3 |  2
 2 |  3
 6 |  4
 7 |  5
 1 |  6

当然，您可以将所有这些更新包装在一个事务中。

如果您限制 pos 以避免重复(一个好主意)，那么您可以使用 pos = 0 作为临时值:

update t set pos = 0 where id = 2;
update t set pos = post + 1 where pos >= 3 and pos < 5;
update t set pos = 3 where id = 2;

或者，如果您使用的是最新版本的 PostgreSQL(AFAIK 9.0+)，您可以 defer your unique constraint到交易结束，而不必担心临时重复。

其他情况类似，留作练习。