使用 MySQL 从树中选择记录

Question

注意：在阅读所有这些附加内容之前，您可能需要跳到底部阅读实际问题。

我正在为 CakePHP 开发 ACL 实现。主要是因为我试图将它与 AuthComponeny 分离，这样我就可以在我的项目中使用 Authsome。我已经掌握了实现的理论，但遇到了一些障碍。

显然我想将数据库查询的数量保持在最低限度。所以我在这里问这是可能的（我严重怀疑它是可能的。）

假设一个像这样的表结构：

id - int(10), auto_increment, primary_key, not null
parent_id - int(10), null
model - varchar(255), utf8_bin, null
foreign_key - int(10), null
alias - varchar(255), utf8_bin, null,
lft - int(10), null
rght - int(10), null

和一些要测试的记录（控制器是根节点，我可能会得到 lft 和 rght 值错误）：

1, null, null, null, controllers,          1,  14
2, 1,    null, null, one_test_controllers, 2,  7
3, 2,    null, null, one_action,           3,  4
4, 2,    null, null, two_action,           5,  6
5, 1,    null, null, two_test_controllers, 8,  13
6, 5,    null, null, one_action,           9,  10
7, 5,    null, null  two_action,           11, 12

还有两个测试路径：

$test1 = '/controllers/one_test_controller/two_action';
$test2 = '/controllers/two_test_controller/two_action';

给出这些结果，从最相关到​​最不相关返回一个 id 数组：

// Result 1
array(
    0 => 4,
    1 => 2,
    2 => 1
)

// Result 2
array(
    0 => 7,
    1 => 5,
    2 => 1
)

我当前正在做的是将路径分解（）到数组中，（在本例中使用 $test1 ）查找与别名“two_action”匹配的所有记录；然后循环遍历结果并查找与最后结果的父 id 匹配且别名为“one_test_controller”的所有记录。然后重复直到parent_id = 0。

它可以工作，但显然多个递归SQL查询并不理想，是否有一个神奇的SQL查询可以帮助我解决这个问题？或者我认为这是它能得到的最好的结果是正确的吗？

Answer 1

嗯？您已经获得了通过使用邻接树一次性解析路径来获取数据的结构。

但是，如果不存储完整路径/需要唯一的节点名称，则无法从下往上搜索。考虑一下 - 在您的两个测试用例中，您都从“two_action”开始，但正在寻找 2 个不同的叶子。如果您将整个路径存储在表中（或者可以通过查询中的 id 引用节点），那么...

SELECT ancestors.*
FROM ahier ancestors,
(SELECT lft, rght
  FROM ahier ref
  WHERE ref.path='/controllers/one_test_controller/two_action') ilv
WHERE (ancestors.lft >= ilv.left AND ancestors.rght <= ilv.rght)
ORDER BY ancestors.lft ASC;

或使用 ids：

SELECT ancestors.*
FROM ahier ancestors,
(SELECT lft, rght
  FROM ahier ref
  WHERE ref.id=4) ilv
WHERE (ancestors.lft >= ilv.left AND ancestors.rght <= ilv.rght)
ORDER BY ancestors.lft ASC;

或者，您可以编写一个查询来返回具有特定节点别名的每个可能路径 - 但是这也不会很有效......

SELECT treenum, ancestors.*
FROM ahier ancestors,
(SELECT lft, rght, id as treenum
  FROM ahier ref
  WHERE ref.alias='two_action') ilv
WHERE (ancestors.lft >= ilv.left AND ancestors.rght <= ilv.rght)
ORDER BY treenum, ancestors.lft ASC;

（并且很容易从parent_ids重建lft和rght）