php - 如何在 Laravel Eloquent 中移动行？

Question

我有一个这样的表:

+--------+-------------------+-----------+
| ID     | Type              |  Order    |
+--------+-------------------+-----------+
| 1      | A                 | 0         |
| 2      | A                 | 1         |
| 3      | A                 | 2         |
| 4      | B                 | 0         |
| 5      | B                 | 1         |
| 6      | B                 | 2         |
| 7      | B                 | 3         |
| 8      | B                 | 4         |
| 9      | C                 | 0         |
| 10     | C                 | 1         |
+--------+-------------------+-----------+

使用 Laravel Eloquent:

$list = Type::where('Type', '=', 'B')
    ->where('Order', '=', '2');

$list->delete();

如果我执行上述操作，我会得到以下结果:

+--------+-------------------+-----------+
| ID     | Type              |  Order    |
+--------+-------------------+-----------+
| 1      | A                 | 0         |
| 2      | A                 | 1         |
| 3      | A                 | 2         |
| 4      | B                 | 0         |
| 5      | B                 | 1         |
| 7      | B                 | 3         | <- notice the Order 2 row is gone
| 8      | B                 | 4         |
| 9      | C                 | 0         |
| 10     | C                 | 1         |
+--------+-------------------+-----------+

我怎样才能使 B 型的顺序相应地变化，所以它看起来像这样？

+--------+-------------------+-----------+
| ID     | Type              |  Order    |
+--------+-------------------+-----------+
| 1      | A                 | 0         |
| 2      | A                 | 1         |
| 3      | A                 | 2         |
| 4      | B                 | 0         |
| 5      | B                 | 1         |
| 7      | B                 | 2         |
| 8      | B                 | 3         |
| 9      | C                 | 0         |
| 10     | C                 | 1         |
+--------+-------------------+-----------+

Answer 1

就数据库而言，您需要运行这两个查询来重新计算 order 列中的值

DELETE FROM types WHERE type='B' AND `order`=2;
UPDATE types SET `order` = `order` - 1 WHERE type='B' AND `order` > 2;

这是一个 SQLFiddle 演示

<小时/>

现在在 Eloquent 中它可能看起来像这样

DB::transaction(function()
{
    Type::where('type', '=', 'B')
        ->where('order', '=', 2)
        ->delete();

    Type::where('type', '=', 'B')
        ->where('order', '>', 2)
        ->update(['order' => DB::raw('`order` - 1')]);
});