How to delete an MPTT Node

03 Sep 2006

By Andy, filed under CakePHP, Tools, ACL, Tips, MPTT, Hacks, SQL

If you use MPTT tables anywhere (The cake acl system uses it) you may have found that when you delete an entry, the lft and rght fields for the neighouring nodes don't get updated. This may have rather dire concequences ;)

There is an open ticket for how to delete from the cake acl tables, but as it is potentially quite generic, I thought I'd include the (potential) fix here. Below are two versions of a delete method, one takes care of associations, and the other doesn't:

// If there are associations to take care of
function 

delete($Alias) {
    $class = $this->name;
    $result = $this->findByAlias($Alias);
    $object = 

$result[$class];
    if ($object == null || count($object) == 0) {
        return false;
    }
    

$idList[] = $result[$class]['id'];

    // won't somebody please think of the children.
    $Children= 

$this->findAll(array("rght"=>"< {$result[$class]['rght']}","lft"=>"> {$result[$class]['lft']}"),"id",null,null,null,-1);
    

foreach($Children as $Child) {
        $idList[] = $Child[$class]['id'];
    }
    // Delete associated 

permissions.
    $this->ArosAco->query("DELETE FROM ".$this->ArosAco->tablePrefix.$this->ArosAco->table." WHERE 

{$class}_id in (".implode(",",$idList).")");

    // Perform the delete
    $table = 

$this->tablePrefix.$this->table;
    $this->query("DELETE FROM $table WHERE $table.lft >= {$result[$class]['lft']} AND 

$table.rght <= {$result[$class]['rght']}");

    // Reindex.
    $shift = 1+ $result[$class]['rght'] - 

$result[$class]['lft'];
    $this->query('UPDATE ' . $table . ' SET `rght` = `rght` - ' . $shift . ' WHERE `rght` > ' 

. $result[$class]['rght']);
    $this->query('UPDATE ' . $table . ' SET `lft` = `lft` - ' . $shift . ' WHERE `lft` 

> ' . $result[$class]['lft']);
    return true;
}

// No associations to take care or
function delete($id) 

{
    $result = $this-read();
    // Perform the delete
    $table = $this->tablePrefix.$this->table;
    

$this->query("DELETE FROM $table WHERE $table.lft >= {$result[$class]['lft']} AND $table.rght <= 

{$result[$class]['rght']}");

    // Reindex.
    $shift = 1+ $result[$class]['rght'] - $result[$class]['lft'];
    

$this->query('UPDATE ' . $table . ' SET `rght` = `rght` - ' . $shift . ' WHERE `rght` > ' . 

$result[$class]['rght']);
    $this->query('UPDATE ' . $table . ' SET `lft` = `lft` - ' . $shift . ' WHERE `lft` > ' . 

$result[$class]['lft']);
    return true;
}

1. // If there are associations to take care of
2. function
3. delete($Alias) {
4. $class = $this->name;
5. $result = $this->findByAlias($Alias);
6. $object =
7. $result[$class];
8. if ($object == null || count($object) == 0) {
9. return false;
10. }
12. $idList[] = $result[$class]['id'];
13. // won't somebody please think of the children.
14. $Children=
15. $this->findAll(array("rght"=>"< {$result[$class]['rght']}","lft"=>"> {$result[$class]['lft']}"),"id",null,null,null,-1);
17. foreach($Children as $Child) {
18. $idList[] = $Child[$class]['id'];
19. }
20. // Delete associated
21. permissions.
22. $this->ArosAco->query("DELETE FROM ".$this->ArosAco->tablePrefix.$this->ArosAco->table." WHERE
23. {$class}_id in (".implode(",",$idList).")");
24. // Perform the delete
25. $table =
26. $this->tablePrefix.$this->table;
27. $this->query("DELETE FROM $table WHERE $table.lft >= {$result[$class]['lft']} AND
28. $table.rght <= {$result[$class]['rght']}");
29. // Reindex.
30. $shift = 1+ $result[$class]['rght'] -
31. $result[$class]['lft'];
32. $this->query('UPDATE ' . $table . ' SET `rght` = `rght` - ' . $shift . ' WHERE `rght` > '
33. . $result[$class]['rght']);
34. $this->query('UPDATE ' . $table . ' SET `lft` = `lft` - ' . $shift . ' WHERE `lft`
35. > ' . $result[$class]['lft']);
36. return true;
37. }
38. // No associations to take care or
39. function delete($id)
40. {
41. $result = $this-read();
42. // Perform the delete
43. $table = $this->tablePrefix.$this->table;
45. $this->query("DELETE FROM $table WHERE $table.lft >= {$result[$class]['lft']} AND $table.rght <=
46. {$result[$class]['rght']}");
47. // Reindex.
48. $shift = 1+ $result[$class]['rght'] - $result[$class]['lft'];
50. $this->query('UPDATE ' . $table . ' SET `rght` = `rght` - ' . $shift . ' WHERE `rght` > ' .
51. $result[$class]['rght']);
52. $this->query('UPDATE ' . $table . ' SET `lft` = `lft` - ' . $shift . ' WHERE `lft` > ' .
53. $result[$class]['lft']);
54. return true;
55. }
56.  

Should anybody use this and find it causes a problem please let me know.