Need help with complex SQL query for moving nested set items

Databases
1

Hi all, I have a database of rows that are organized via Nested sets. This is from a site that was suppose to be a multi-tier affiliate site, but the prior programmer made the decision to use nested sets instead of a simple tier system, so virtually every row affects every other row in terms of positioning, and this is a big pain when trying to move a tiered member to a different tier. The code below is supposed to move a nested set from one to another, but the problem is after I have ran the code the left and right values are not what they are supposed to be, they are thousands of numbers off. Another difficulty is each row is related to another row in the same table. Below is the SQL code that I hacked together from another post on here, so all help would be appreciated.

# step 0: Initialize parameters.
SELECT
    @member_id := e1.memberid,
    @member_lft := e1.lft,
    @member_rgt := e1.rgt,
    @parent_id := e2.memberid,
    @parent_rgt := e2.rgt,
    @member_size := @member_rgt - @member_lft + 1
FROM ept_viewer e1
LEFT JOIN ept_viewer e2
#put the new parent id
ON e2.memberid = $
#put the child id
WHERE e1.memberid = $;

# step 1: temporary "remove" moving node
UPDATE `ept_viewer`
SET `lft` = 0-(`lft`), `rgt` = 0-(`rgt`)
WHERE `lft` >= @member_lft AND `rgt` <= @member_rgt;

# step 2: decrease left and/or right position values of currently 'lower' items (and parents)
UPDATE `ept_viewer`
SET `lft` = `lft` - @member_lft
WHERE `lft` > @member_rgt;
UPDATE `ept_viewer`
SET `rgt` = `rgt` - @member_size
WHERE `rgt` > @member_rgt;

# step 3: increase left and/or right position values of future 'lower' items (and parents)
UPDATE `ept_viewer`
SET `lft` = `lft` + @member_size
WHERE `lft` >= IF(@parent_rgt > @member_rgt, @parent_rgt - @member_size, @parent_rgt);
UPDATE `ept_viewer`
SET `rgt` = `rgt` + @member_size
WHERE `rgt` >= IF(@parent_rgt > @member_rgt, @parent_rgt - @member_size, @parent_rgt);

# step 4: move node (ant it's subnodes) and update it's parent item id
UPDATE `ept_viewer`
SET
    `lft` = 0-(`lft`)+IF(@parent_rgt > @member_rgt, @parent_rgt - @member_rgt - 1, @parent_rgt - @member_rgt - 1 + @member_size),
    `rgt` = 0-(`rgt`)+IF(@parent_rgt > @member_rgt, @parent_rgt - @member_rgt - 1, @parent_rgt - @member_rgt - 1 + @member_size)
WHERE `lft` <= 0-@member_lft AND `rgt` >= 0-@member_rgt;
2

I normally recommend the The Nested Set Model over the Adjacency List Model for storing hierachal data in a database.

Both models have their pros and cons but imho the pros of the nested set model (using left and right values) to represent the tree structure make life a lot easier in the long run.

Anyway, I posted the sql code I use to move a node and all its children as

  1. a sibling to another node

or

  1. a child to a leaf node

in this thread