How to work with nested sets?

Question

Please do not give links to articles, since they have already been read, and the essence of the question in those moments that I did not understand.

I created the table:

create table `category` not exists ( `id` int(11) not null auto_increment, `name` varchar(255) not null, `left_key` int(11) not null, `right_key` int(11) not null, `level` int(11) not null default 0, primary key (`id`) ) engine=myisam default charset=utf8 auto_increment=1;

Next, make a small dump:

 insert into `category` (`name`, `level`) values ('Категория 1', 0), ('Категория 2', 0), ('Категория 3', 0), ('Подкатегория 1', 1), ('Подкатегория 2', 1), ('Подкатегория 3', 1), ('Подкатегория 1', 2), ('Подкатегория 2', 2), ('Подкатегория 3', 2), ('Подкатегория 4', 2), ('Подкатегория 5', 2), ('Подкатегория 1', 3), ('Подкатегория 2', 3), ('Подкатегория 3', 3), ('Подкатегория 1', 4), ('Подкатегория 2', 4), ('Подкатегория 3', 4);

How now to update the left and right keys? I would be grateful for clarification in accessible language for the average user.

Yura Ivanov Yura Ivanov 25.2k one 22 52 · Accepted Answer · 2014-10-23T15:17:41

Your dump does not contain information about the subordination of nodes. Therefore, you will not update the left and right key. And the keys need to be updated when adding each record, and not after ...

For each addition of a node, you can independently perform several requests (yes, yes, an article about them is written) in a row, or add data by such a procedure:

 CREATE PROCEDURE category_add(IN _parent int, IN _name varchar(150)) BEGIN declare $right_key, $level int; SELECT IFNULL(right_key,1),IFNULL(level,0) INTO $right_key,$level FROM category WHERE id = _parent; SET $right_key = IFNULL($right_key,1); SET $level = IFNULL($level,0); UPDATE category SET right_key = right_key + 2, left_key = IF(left_key > $right_key, left_key + 2, left_key) WHERE right_key >= $right_key; INSERT INTO category (left_key,right_key,level,name) VALUES ($right_key, $right_key + 1, $level + 1,_name); END

Fiddle

How to work with nested sets?

1 answer 1

More articles: