php - 使用 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

还有一些要测试的记录( Controller 是根节点，我可能会弄错 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;

或使用 ID:

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)