SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need help with complex SQL query for moving nested set items.

    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.

    Code SQL:
    # 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;
    Have a good day.

  2. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    2) a child to a leaf node

    in this thread


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •