jQuery nestable - 使用PHP嵌套集模型保存到MySQL [关闭]

Question

I use now Nestable plugin for eshop categories http://dbushell.github.io/Nestable/

I save data to MySQL database with columns parent_id, order, level using the Nested Set model.

But I would like to use nested model only with columns lft, rgt

Could you please gave me example how to save data to database or is there any wrapper which can I use to just insert new item, update all tree and delete one item, using the Nested Set model?

---

I found Baobab library http://www.sideralis.org/baobab/, which do these operations, but it's very heavy.

Answer 1

Here...

To Insert:

begin;
SELECT rgt INTO new_lft FROM tree_map WHERE node_id = parent_id;
UPDATE tree_map SET rgt = rgt + 2 WHERE rgt >= new_lft;
UPDATE tree_map SET lft = lft + 2 WHERE lft > new_lft;
INSERT INTO tree_map (lft, rgt, parent_id) VALUES (new_lft, (new_lft + 1), parent_id);
commit;
SELECT LAST_INSERT_ID();

To delete:

SELECT lft, rgt, (rgt - lft), (rgt - lft + 1), parent_id 
  INTO new_lft, new_rgt, has_leafs, width, superior_parent 
  FROM tree_map WHERE node_id = pnode_id;

begin;
DELETE FROM tree_content WHERE node_id = pnode_id;

IF (has_leafs = 1) THEN
  DELETE FROM tree_map WHERE lft BETWEEN new_lft AND new_rgt;
  UPDATE tree_map SET rgt = rgt - width WHERE rgt > new_rgt;
  UPDATE tree_map SET lft = lft - width WHERE lft > new_rgt;
ELSE
  DELETE FROM tree_map WHERE lft = new_lft;
  UPDATE tree_map SET rgt = rgt - 1, lft = lft - 1, parent_id = superior_parent 
   WHERE lft BETWEEN new_lft AND new_rgt;
  UPDATE tree_map SET rgt = rgt - 2 WHERE rgt > new_rgt;
  UPDATE tree_map SET lft = lft - 2 WHERE lft > new_rgt;
END IF;
commit;

You might also want to use amazing stored procedure for MySQL instead:

https://github.com/werc/TreeTraversal/blob/master/sql/r_tree_traversal.sql#L4