tree_append($parent_id, \%values)

Appends a new node in the subtree of the specified parent. If $parent_id is undef, it will add a root node. When you want to add a root node you can as well omit specifying the $parent_id (our code will realize that the first argument is a reference).

$values is a hash as required by DBIx::OO::create().

Examples:

    $node = Category->tree_append({ label => 'electronics' });
    $node = Category->tree_append(undef, { label => 'electronics' });
    $lcd = Category->tree_append($tvs->id, { label => 'lcd' });
    $lcd->tree_append({ label => 'monitors' });

As you can see, you can call it both as a package method or as an object method. When you call it as a package method, it will look at the type of the first argument. If it's a reference, it will guess that you want to add a root node. Otherwise it will add the new node under the specified parent.

Beware of mistakes! Do NOT call it like this:

    $tvs = Category->search({ label => 'televisions' })->[0];
    Category->tree_append($tvs, { label => 'lcd' });

If you specify a parent, it MUST be its ID, not an object!

tree_insert_before, tree_insert_after ($anchor, \%values)

Similar in function to tree_append, but these functions allow you to insert a node before or after a specified node ($anchor).

Examples:

    $lcd->tree_insert_after({ label => 'plasma' });
    $lcd->tree_insert_before({ label => 'tube' });
    # Or, as a package method:
    Category->tree_insert_after($lcd->id, { label => 'plasma' });
    Category->tree_insert_before($lcd->id, { label => 'tube' });

Note that specifying the parent is not required, because it's clearly that the new node should have the same parent as the anchor node.

tree_reparent($source_id, $dest_id)

This function will remove the $source node from its current parent and append it to the $dest node. As with the other functions, you can call it both as a package method or as an object method. When you call it as an object method, it's not necessary to specify $source.

You can specify undef for $dest_id, in which case $source will become a root node (as if it would be appended with tree_append(undef)).

No nodes are DELETE-ed nor INSERT-ed by this function. It simply moves existing nodes, which means that any node ID-s that you happen to have should remain valid and point to the same nodes. However, the tree structure is changed, so if you maintain the tree in memory you have to update it after calling this funciton. Same applies to tree_move_before() and tree_move_after().

Examples:

    # the following are equivalent
    Category->tree_reparent($lcd->id, $plasma->id);
    $lcd->tree_reparent($plasma->id);

This function does a lot of work in order to maintain the tree integrity, therefore it might be slow.

NOTE: it doesn't do any safety checks to make sure moving the node is allowed. For instance, you can't move a node to one of its child nodes.

tree_move_before, tree_move_after ($source_id, $anchor_id)

These functions are similar to a reparent operation, but they allow one to specify where to put the $source node, in the subtree of $anchor's parent. See tree_reparent().

Examples:

    $portable->tree_move_before($electronics->id);
    Category->tree_move_after($lcd->id, $flash->id);

tree_delete($node_id)

Removes a node (and its full subtree) from the database.

Equivalent examples:

    Category->tree_delete($lcd->id);
    $lcd->tree_delete;

tree_get_subtree(\%args)

Retrieves the full subtree of a specified node. $args is a hashref that can contain:

 - parent : the ID of the node whose subtree we want to get
 - where  : an WHERE clause in SQL::Abstract format
 - limit  : allows you to limit the results (using SQL LIMIT)
 - offset : SQL OFFSET
 - fields : (arrayref) allows you to specify a list of fields you're
            interested in

This can be called as a package method, or as an object method.

Examples first:

    $all_nodes = Category->tree_get_subtree;
    $nodes = Category->tree_get_subtree({ parent => $portable->id });
    ## OR
    $nodes = $portable->tree_get_subtree;
    # Filtering:
    $nodes = Category->tree_get_subtree({ where => { label => { -like => '%a%' }}});
    # Specify fields:
    $nodes = Category->tree_get_subtree({ fields => [ 'label' ] });

This function returns an array of hashes that contain the fields you required. If you specify no fields, 'id' and 'parent' will be SELECT-ed by default. Even if you do specify an array of field names, 'id' and 'parent' would still be included in the SELECT (so you don't want to specify them).

Using this array you can easily create DBIx::OO objects (or in our sample, Category objects):

    $arrayref = Category->init_from_data($nodes);

OK, let's get to a more real-world example. Suppose we have a forum and we need to list all messages in a thread ($thread_id). Here's what we're going to do:

    $data = ForumMessage->tree_get_subtree({
        parent => $thread_id,
        fields => [qw( subject body author date )],
    });
    # the above runs one SQL query
    $objects = ForumMessage->init_from_data($data);
    # the above simply initializes ForumMessage objects from the
    # returned data, B<without> calling the database (since we have
    # the primary key automatically selected by tree_get_subtree, and
    # also have cared to select the fields we're going to use).
    # compute the level of each message, to indent them easily
    $levels = ForumMessage->tree_compute_levels($data);
    # and now display them
    foreach my $msg (@$objects) {
        my $class = 'level' . $levels{$msg->id};
        print "<div class='$class'>", $msg->subject, "<br><br>",
              $msg->body, "<br><br>By: ", $msg->author, "</div>";
    }
    # and indentation is now a matter of CSS. ;-) (define level0,
    # level1, level2, etc.)

All this can be done with a single SQL query. Of course, note that we didn't even need to initialize the $objects array--that's mainly useful when you want to update the database.

tree_get_path(\%args)

Retrieves the path of a given node. $args is an hashref that can contain:

 - id     : the ID of the node whose path you're interested in
 - fields : array of field names to be SELECT-ed (same like
   tree_get_subtree)

This returns data in the same format as tree_get_subtree().

tree_get_next_sibling, tree_get_prev_sibling

XXX: this info may be inaccurate

Return the next/previous item in the tree view. $args has the same significance as in "tree_get_path". $args->{id} defines the reference node; if missing, it's assumed to be $self.

tree_get_next, tree_get_prev

XXX: this info may be inaccurate

Similar to "tree_get_next_sibling" / "tree_get_prev_sibling" but allow $args->{where} to contain a WHERE clause (in SQL::Abstract format) and returns the next/prev item that matches the criteria.

tree_compute_levels($data)

This is an utility function that computes the level of each node in $data (where $data is an array reference as returned by tree_get_subtree or tree_get_path).

This is generic, and it's simply for convenience--in particular cases you might find it faster to compute the levels yourself.

It returns an hashref that maps node ID to its level.

In [1] we can see there is a method to compute the subtree depth directly in SQL, I will paste the relevant code here:

  SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
  FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
  WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
  GROUP BY node.name
  ORDER BY node.lft;

I find it horrible.