在一个视图中将树结构组模型与其HABTM用户模型结合起来

Question

我有一个树结构的模型组，它有列id、parent_id、name、lft和rght。我还有一个模型User，它有id、name和password列。Group和User使用连接表groups_users具有一个具有和属于多个(HABTM)的关系。

我想要创建一个视图，该视图创建一个新树，它包含所有组和作为其所属组的子组的所有用户。视图应该有列id、parent_id、model、foreign_key、lft和rght。

id列应该是第N行，parent_id、lft和rght不能仅仅从组模型中复制，它们必须为视图的树进行计算。

视图可能是什么样子的示例：

| id | parent_id | model | foreign_key | lft | rght
----------------------------------------------------
|  1 |    NULL   | Group |      1      |  1  |  14  
|  2 |     1     | User  |      1      |  2  |   3  
|  3 |     2     | Group |      2      |  4  |   7  
|  4 |     3     | User  |      3      |  5  |   6  
|  5 |     4     | Group |      3      |  8  |  13  
|  6 |     5     | User  |      2      |  9  |  10  
|  7 |     5     | User  |      4      | 11  |  12  
|  8 |    NULL   | Group |      4      | 15  |  20  
|  9 |     8     | User  |      4      | 16  |  17  
| 10 |     8     | User  |      5      | 18  |  19  

我该怎么做？

它能用一些超级复杂的SELECT语句来完成吗?或者我需要使用一个过程？

我在MySQL并不是超级熟练；我可以做简单的SELECTs和JOINs，但这远远超出了我的技能水平。

Answer 1

这不能用一个视图来完成。

MySQL中的视图不能引用变量。即使定义一个引用会话变量的简单视图，也没有喜悦。以引用变量的工作查询为例，尝试使用该查询创建视图：

ERROR 1351 (HY000): View's SELECT contains a variable or parameter

当然，这里有一个黑客:您可以调用您编写的存储函数，它返回您想要被视图访问的变量的值。但是，哎呀，我们已经不属于“视图”的范围了。

此外，与查询一样，视图无法遍历行并在"this“行中执行某些操作，因为在”那个“行中发生了什么。

当然，查询中也有一些变量可以模拟某些行为，但在最好的时候，它们只能回顾而不是向前看，而且它们并不总是按照返回行的相同顺序“看到”行。

我看到了三种可能的方法，如果SQL是您真正想要这样做的地方：

选项1涉及存储过程和临时表。存储过程可以使用游标遍历行，并且您需要一个临时表，因为存储过程没有数组或散列。迭代您的源数据，或者使用一个游标，或者在一个循环中，首先找到最高级的父节点，然后根据到目前为止所发现的内容，每次后续选择.一边填充临时表，一边采取预防措施，避免由parent_id值中的循环引用造成的无限循环( go！)，然后从proc中的临时表中选择将其内容作为结果集返回给客户端。

您甚至可能会从第一个存储过程调用第二个存储过程，然后递归地调用它自己，遍历树并构建嵌套集。

选项2涉及组和用户表上的insert、update和delete触发器，每次对组和用户进行任何修改时，都会重新构建MPTT结构。

从某种意义上说，这将是“最正确”的方法，因为您的左、右in永远不会与底层数据不一致.但正如@dogmatic69所指出的，这是一个非常昂贵的手术.而且也不是没有它自己的障碍，因为当您在table_x上的触发器中时，您只能使用table_x。

另一方面，使用此选项，当您需要查看树时，构建它的工作已经完成.所以，选择要快得多。

选项3是用SQL以外的东西计算树值.所以，真的，我之前撒了谎，我只想出了两个SQL选项。用PHP、Perl等计算所有这些，然后用计算出来的值填充数据库，这就是我通常所做的。)但是我可以不使用它，因为我的数据库中的父/子关系不会被任何其他进程更新。如果是的话，我会盯着选项2的枪管。