Updating Nested Sets When Parent Changes or Deletes

Question

I have the following table structure, which is also for sqlfiddle for convenience:

  + --------- + ----------- + --------- + ---------- + ------ ----- + ------------------- + ------------------------ ----------- + -------- + -------------- + ------------- + --------------- + ----------- +
 |  rule_id |  parent_id |  left_id |  right_id |  rule_type |  rule_title |  rule_description |  public |  parse_bbcode |  parse_links |  parse_smilies |  group_ids |
 + --------- + ----------- + --------- + ---------- + ------ ----- + ------------------- + ------------------------ ----------- + -------- + -------------- + ------------- + --------------- + ----------- +
 |  1 |  0 |  1 |  6 |  cat |  Sample Category 1 |  |  1 |  0 |  0 |  0 |  1 2 7 |
 |  2 |  1 |  2 |  3 |  rule |  Sample Rule 1 |  This is a sample rule description |  1 |  1 |  1 |  1 |  1 2 7 |
 |  3 |  0 |  7 |  8 |  cat |  Sample category 2 |  |  1 |  0 |  0 |  0 |  1 7 2 |
 |  4 |  0 |  9 |  10 |  cat |  Sample category 3 |  |  1 |  0 |  0 |  0 |  1 7 2 |
 |  5 |  1 |  4 |  5 |  rule |  Sample rule 3 |  lol |  1 |  1 |  1 |  1 |  1 2 7 |
 + --------- + ----------- + --------- + ---------- + ------ ----- + ------------------- + ------------------------ ----------- + -------- + -------------- + ------------- + --------------- + ----------- +

As you can see, rule_type can be either 'cat' or 'rule' .

cat denotes a category , and categories are the root nodes: therefore parent_id always 0 . In my code, we can identify categories by checking either if rule_type = 'cat' or parent_id = 0 .

You can also see that I am using nested sets for my project, and that is the problem.

I have successfully created functions that:

• Move rules and categories up or down and

• Add a new rule or category at the end of your place.

BUT I cannot set the RULES right_id and left_id if we change its parent_id ! I also cannot set right_id and left_id if we remove the rule OR categories.

Example

I will try to explain with an example. Please note that this is just an example, not an actual case, and I need a general answer.

From the table above, we see that we have 3 categories with rule_id IN (1, 3, 4) and two rules with rule_id IN (2, 5) .

The rule with rule_id = 2 is part of the category with rule_id = 1 , this can be seen from the parent_id column. What if I change parent_id to 4? How would I set right_id and left_id so that everything is in place again? I know that we need to update as rule_id IN (1, 4) in order to reorder, but I don’t know what my query will look like.

The same thing happens for deletion ... For example, I delete rule_id = 2 (this is a rule ), how would I set right_id and left_id for parent_id = 1 in the correct order? Or when I delete a category? How to reorder categories?

I really didn’t do anything here because I don’t have any vision of how I would do this, so I ask for help guys.

I hope I get it. If not, let me know and I will try to be more visual.

Answer 1

I assume that you have successfully configured the PDO connection.

Also, remember that the following examples only work if all categories are root nodes (for example, in a topic). No problem changing this code to work with nested categories.

Delete rule

• Get the value of right_id and left_id .

• Delete row from database.

• Update the set of tables right_id - 2 where right_id greater than right_id to delete the rule.

• The same for left_id

Example:

  $ruleIdForDel = 2; $leftId = 2; $rightId = 3; $pdo->beginTransaction(); try { $pdo->exec("DELETE FROM rules WHERE rule_id = $ruleIdForDel"); $pdo->exec("UPDATE rules SET left_id = CASE WHEN left_id > $leftId THEN left_id - 2 ELSE left_id END, right_id = CASE WHEN right_id > $rightId THEN right_id - 2 ELSE right_id END"); $pdo->commit(); } catch (Exception $e) { $pdo->rollBack(); throw $e; } 

parent_id update parent_id

Suppose we move the node to the last position of the new parent

• Get update rule left_id and right_id ( $ruleLeftId and $ruleRightId )

• Get the new parent rule left_id and right_id ( $newParentRuleLeftId and $newParentRuleRightId )

• Check the node moving up or down the tree and depending on it generate new values left_id and right_id for the update rule ( $ruleNewLeftId and $ruleNewRightId )

• Update rules left_id and right_id depending on the purpose of the update rule

• Update parent_id , left_id , right_id update rules

If the update rule right_key less than the new parent rule right_id , then the rule moves down the tree, otherwise it moves up the tree.

If the rule moves down the tree, we are going to shift the left_id value by minus 2 according to the rules located between the update rule left_id ( $ruleLeftId ) and the new left_id ( $ruleNewLeftId ) + 1 Otherwise, left_id value by 2 according to the rules located between the new left_id ($ ruleNewLeftId) and initial left_id ( $ruleLeftId ).

The same goes for right_id .

Example:

  // Updating rule $ruleId = 2; $ruleLeftId = 2; $ruleRightId = 3; // New parent rule $newParentRuleId = 3; $newParentRuleLeftId = 7; $newParentRuleRightId = 8; // Generate new rule left and right keys // Moves up if ($newParentRuleRightId < $ruleRightId) { $ruleNewLeftId = $newParentRuleRightId; $ruleNewRightId = $newParentRuleRightId + 1; // Moves down } else { $ruleNewLeftId = $newParentRuleRightId - 2; // 6 $ruleNewRightId = $newParentRuleRightId - 1; // 7 } $pdo->beginTransaction(); try { $pdo->exec("UPDATE rules SET left_id = CASE /* Moves down */ WHEN $ruleNewRightId > $ruleRightId AND left_id > $ruleLeftId AND left_id <= $ruleNewLeftId + 1 THEN left_id - 2 /* Moves up */ WHEN $ruleNewRightId < $ruleRightId AND left_id >= $ruleNewLeftId AND left_id < $ruleLeftId THEN left_id + 2 ELSE left_id END, right_id = CASE WHEN $ruleNewRightId > $ruleRightId AND right_id > $ruleRightId AND right_id <= $ruleNewRightId THEN right_id - 2 WHEN $ruleNewRightId < $ruleRightId AND right_id >= $ruleNewLeftId AND right_id <= $ruleRightId THEN right_id + 2 ELSE right_id END"); $pdo->exec("UPDATE rules SET parent_id = $newParentRuleId, left_id = $ruleNewLeftId, right_id = $ruleNewRightId WHERE rule_id = $ruleId"); $pdo->commit(); } catch (Exception $e) { $pdo->rollBack(); throw $e; } 

I did not use PDO::Statement just to save space.

I have not tested it properly, so send a message if you find it.

Answer 2

A rule with rule_id = 2 is part of a category with rule_id = 1, we can see this from the parent_id column. What if i change parent_id to 4? How would you set right_id and left_id so that everything is in place again? I know that we need to update both rule_id IN (1, 4) and reorder everything, but I don’t know what my query will look like.

Try something like this, I haven’t tested it yet, but it may get on the right track

 UPDATE table SET parent_id = 4, right_id = (CASE WHEN parent_id = 4 THEN 1 END), left_id = (CASE WHEN parent_id = 4 THEN 2 END)