MySql闭包表将不支持针对不同父级的重复子类别 [英] MySql closure table won't support duplicate sub-category for different parents

问题描述

我已经和这个封闭表一起转了一圈了.我遇到的问题是后代的第二次出现.我有出现在多个父类别中的子类别实例.为了简单起见，我已回到此示例:

I have been going around in circles with this closure table for awhile. The problem I have is with the second occurrence of a descendant. I have instances of sub-categories that appear in more than one parent category. I have reverted to this example for simplicity:

drop table if exists closure;
drop table if exists nodes;

create table nodes (
node int auto_increment primary key,
label varchar(20) not null
);

insert into nodes (node, label) values
(1, 'rootree'),
(2, '1stbranch'),
(3, 'midbranch'),
(4, 'corebranch'),
(5, 'leafnodes'),
(6, 'lastbranch'),
(7, 'lastleaf');

create table closure (
ancestor int not null,
descendant int not null,
primary key (ancestor, descendant),
foreign key (ancestor) references nodes(node),
foreign key (descendant) references nodes(node)
);

insert into closure (ancestor, descendant) values
(1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (1,7),
(2,2),
(3,3), (3,4), (3,5),
(4,4), (4,5),
(5,5),
(6,6), (6,7),
(7,7);

使用以下查询，可以得到所需的结果:

Using the folowing query, I can get the desired results:

select group_concat(n.label order by n.node separator ' -> ') as path
from closure d
join closure a on (a.descendant = d.descendant)
join nodes n on (n.node = a.ancestor)
where d.ancestor = 1 and d.descendant != d.ancestor
group by d.descendant;

结果:

rootree -> 1stbranch
rootree -> midbranch
rootree -> midbranch -> corebranch
rootree -> midbranch -> corebranch -> leafnodes
rootree -> lastbranch
rootree -> lastbranch -> lastleaf

但是，如果我添加另一个孩子，例如已经存在的孩子，我想将leafnodes设为roottree-> lastbranch-> lastleaf

But if I add another child, a child that already exists, for instance, I want to make leafnodes a child of roottree -> lastbranch -> lastleaf

我将两个新记录插入到关闭表中: (6-5)和(7-5)

I insert two new records into the closure table: (6-5) and (7-5)

然后所有的地狱都松散了.我已经尽我所能想到的一切，但是我什么都没得到.

Then all hell breaks loose. The I have tried everything I can think of but I'm not getting anywhere.

推荐答案

我在这里找到了答案:

I found the answer here: Converting the Closure Table from a Weak Entity

答案::我向闭合表添加了两个字段，以保存祖先和后代的ID.现在，节点独立于实际值.该结构保持连接状态，并用于插入，删除，查询等，但是保留在每个节点上的值是它们自己的实体，可以用任何其他值替换而不影响树的结构.这使我得以解决自己的特殊问题，即在多个父类别中使用相同的子类别.我希望还有许多其他好处，但尚未发现.

ANSWER: I added two fields to the closure table to hold the id of both the ancestor and descendant. Now the nodes are independent of the actual values. The structure remains connected and is used for inserting, deleting, querying, etc., but the values that are retained at each node are their own entity and can be replaced by any other without affecting the structure of the tree. This allowed me to solve my particular problem, which was using the same child category in multiple parent categories. I expect that there are many other benefits, yet to be discovered.

这篇关于MySql闭包表将不支持针对不同父级的重复子类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！