PDO中的级联数据更新 - Cascade data update in PDO

我有一个MySql表，其中包含使用修改后的预序树遍历方法排序的节点。每个节点都有一个 id、一个左值和一个右值。

+---------+-----+-----+
| id      | lft | rgt |
+---------+-----+-----+
| root    | 1   | 20  |
| father1 | 2   | 7   |
| child1  | 3   | 4   |
| child2  | 5   | 6   |
| father2 | 8   | 9   |
| father3 | 10  | 17  |
| child3  | 11  | 12  |
| child4  | 13  | 14  |
| child5  | 15  | 16  |
| father4 | 18  | 19  |
+---------+-----+-----+

我的目标是在father3之后带来father1，并获得这个结果。

+---------+-----+-----+
| id      | lft | rgt |
+---------+-----+-----+
| root    | 1   | 20  |
| father2 | 2   | 3   |
| father3 | 4   | 11  |
| child3  | 5   | 6   |
| child4  | 7   | 8   |
| child5  | 9   | 10  |
| father1 | 12  | 17  |
| child1  | 13  | 14  |
| child2  | 15  | 16  |
| father4 | 18  | 19  |
+---------+-----+-----+

要执行此操作，我需要：

递增father1（和后代）10的左右值
左右递减father2和father3（及其后代）6值

我正在尝试使用 PDO 执行此更新。

$left = 2; // father1 left value
$right = 7; // father1 right value
$drop = 18; // insert point value (will be after new father1 position)
$delta = $right - $left + 1; // 6 
$gap = $drop - $right - 1; // 10
$dbms->beginTransaction();
// increment father1 (and descendants)
$dbms->prepare("UPDATE categories SET lft=lft+:gaplft, rgt=rgt+:gaprgt WHERE lft>=:startlft AND lft<:endlft;");
$dbms->bindparam(':gaplft', $gap, PDO::PARAM_INT);
$dbms->bindparam(':gaprgt', $gap, PDO::PARAM_INT);
$dbms->bindparam(':startlft', $left, PDO::PARAM_INT);
$dbms->bindparam(':endlft', $right+1, PDO::PARAM_INT);
$dbms->query();
// decrement father2 and father3 (and descendants)
$dbms->prepare("UPDATE categories SET lft=lft-:deltalft, rgt=rgt-:deltargt WHERE lft>=:startlft AND lft<:endlft;");
$dbms->bindparam(':deltalft', $delta, PDO::PARAM_INT);
$dbms->bindparam(':deltargt', $delta, PDO::PARAM_INT);
$dbms->bindparam(':startlft', $right+1, PDO::PARAM_INT);
$dbms->bindparam(':endlft', $drop, PDO::PARAM_INT);
$dbms->query();
$dbms->commit();

这无法按预期工作。结果如下：

+---------+-----+-----+
| id      | lft | rgt |
+---------+-----+-----+
| root    | 1   | 20  |
| father2 | 2   | 3   |
| father3 | 4   | 11  |
| child3  | 5   | 6   |
| child4  | 7   | 8   |
| child5  | 9   | 10  |
| father1 | 6   | 11  |  // should be 12 17
| child1  | 7   | 8   |  // should be 13 14
| child2  | 9   | 10  |  // should be 15 16
| father4 | 18  | 19  |
+---------+-----+-----+

我认为这可能与更新的并发性有关。第一次更新后，只有father1（和后代）设置为最终值。第二次更新只应该影响father2和father3（以及后代），实际上也会影响father1。

有没有办法在PDO中处理级联更新？

您在两个查询中都在 WHERE 上定义了相同的间隔，这就是问题所在：

WHERE lft>=:startlft AND lft<:endlft;

因此，在第一个查询中，它将gap添加到 father 1 left -> 2+10 = 12。父亲 1 留下现在是 12，如你所愿。然后，在第二个查询中，不是从father 2 left和father 3 left中获取delta，而是从 father 1 left -> 12-6 = 6 中获取delta。父亲 1 左现在是 6。父亲 1 右，7+10=17 然后 17-6 = 11 也会发生同样的情况。第二个查询不会更新父 2 和 3，因为它们不符合其上提供的间隔。

您必须更正 WHERE 在第二次查询中选择的间隔，以便它可以更新父项 2 和 3，而不是更新父项 1。将第二个查询更改为此查询，这必须解决问题。

// decrement father2 and father3 (and descendants)
$dbms->prepare("UPDATE categories SET lft=lft-:deltalft, rgt=rgt-:deltargt WHERE lft<:startlft OR lft>=:endlft;");

经过一番思考，我意识到所需的操作等效于在循环区间内求和。

如果我们考虑区间(lft,rgt) = [2,18)，向所有节点（和后代）添加足够的量以将father1（和后代）放置在区间(18)的顶部，我们可以得到所需的结果。为此，我们需要

计算将father1（和后代）移动到顶部所需的数量。这等于father1和顶部之间的"距离"：18-8 = 10
将此数量添加到所有节点
向下平移所有节点，让区间的底部与0重合。在这种情况下，我们需要从所有节点中减去2。下一步（重要）需要此操作
在所有节点上执行modulo操作。modulo的除数必须等于区间的宽度（在我们的例子中为 18-2 = 16 ）。这允许father2和father3从间隔的顶部退出，并从底部重新进入，father1保持不变
再次向上平移2的区间以重新建立原始底部值

让我们看一下实现它的代码。

$left = 2; // father1 left value
$right = 7; // father1 right value
$drop = 18; // insert point value
$gap = $drop - $right - $left - 1; // distance between father1 and the top, minus the initial offset (equal to the bottom of interval)
$size = $drop - $left; // 16, size of the interval
$dbms->prepare("UPDATE categories SET lft=:offsetlft+MOD(lft+:gaplft,:sizelft), rgt=:offsetrgt+MOD(rgt+:gaprgt,:sizergt) WHERE lft>=:startlft AND lft<:endlft;");
$dbms->bindparam(':offsetlft', $left, PDO::PARAM_INT);
$dbms->bindparam(':offsetrgt', $left, PDO::PARAM_INT);
$dbms->bindparam(':gaplft', $gap, PDO::PARAM_INT);
$dbms->bindparam(':gaprgt', $gap, PDO::PARAM_INT);
$dbms->bindparam(':sizelft', $size, PDO::PARAM_INT);
$dbms->bindparam(':sizergt', $size, PDO::PARAM_INT);
$dbms->bindparam(':startlft', $left, PDO::PARAM_INT);
$dbms->bindparam(':endlft', $drop, PDO::PARAM_INT);
$dbms->query();

由于我们只有一个查询，因此我们可以删除beginTransaction()和commit()语句。结果如预期

+---------+-----+-----+
| id      | lft | rgt |
+---------+-----+-----+
| root    | 1   | 20  |
| father2 | 2   | 3   |
| father3 | 4   | 11  |
| child3  | 5   | 6   |
| child4  | 7   | 8   |
| child5  | 9   | 10  |
| father1 | 12  | 17  |
| child1  | 13  | 14  |
| child2  | 15  | 16  |
| father4 | 18  | 19  |
+---------+-----+-----+

简单快捷。希望它对某人有所帮助。