Using nested set model to store Hierarchical data in sqlite how can I move a category into another category

Question

I'm trying to store hierarchical data using SQLite. After searching a lot, I chose to use the nested set model instead of an adjacency list, because almost 90% of the operations will be reads and only 10% will be updates/deletes/creates.

I followed this example: http://www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html

And it works fine to add, delete and read new nodes.

But I didn't find any article explaining how to update the tree, e.g. moving a category into another category.

Below is my database structure:

id   name   left_node   right_node
1    name1     1          2

** I didn't find a place explaining how to update the hierarchy, which I really need. **

Another problem is

public function delete_node($pleft, $pright){

$width = $pright-$pleft+1;

$delete_sql = "delete from categories where left_node between $pleft and $pright";
$update_sql1 = "update categories set right_node = right_node-$width where right_node > $pright";
$update_sql2 = "update categories set left_node = left_node-$width where left_node> $pright";
//
$this->db->trans_start();
//
$this->db->query($delete_sql);

//
$this->db->query($update_sql1);
$this->db->query($update_sql2);
$this->db->trans_complete();
//
return $this->db->trans_status();
}

This my delete method, and it takes 30ms to finish. Is that normal?

I solved the problem, thanks for the help https://rogerkeays.com/how-to-move-a-node-in-nested-sets-with-sql

I'm using codeigniter with sqlite database. below is my function,

public function move_node($pleft, $pright, $origin_left_pos, $origin_right_pos){

//
//the new_left_position is different according to which way you want to move the node 
$new_left_position = $pleft + 1;
//
$width = $origin_right_pos - $origin_left_pos + 1;
$temp_left_position = $origin_left_pos;

$distance = $new_left_position - $origin_left_pos;
//backwards movement must account for new space
if($distance < 0){

  $distance -= $width;
  $temp_left_position += $width;

}
//
$update_sql1 = "update categories set left_node = left_node+$width where left_node >=  $new_left_position";

$update_sql2 = "update categories set right_node = right_node+$width where right_node >= $new_left_position";

//
$update_sql3 = "update categories set left_node = left_node+$distance , right_node = right_node+$distance where left_node >= $temp_left_position AND right_node < $temp_left_position+$width";

//
$update_sql4 = "update categories set left_node = left_node-$width where left_node > $origin_right_pos";
$update_sql5 = "update categories set right_node = right_node-$width where right_node > $origin_right_pos";

//

$this->db->trans_start();

$this->db->query($update_sql1);

//
$this->db->query($update_sql2);
$this->db->query($update_sql3);
$this->db->query($update_sql4);
$this->db->query($update_sql5);
$this->db->trans_complete();

return $this->db->trans_status();
}

Answer 1

There are a couple of answers in SO exactly about your problem:

Move node in nested set

Move node in Nested Sets tree

About the time your delete method is taking to run, 30ms it's very little for this kind of operation, so there's nothing to worry about. Don't fall into the trap of premature optimization. :)