DBIx::Tree::MaterializedPath - fast DBI queries and updates on "materialized path" trees


Version 0.06


    use DBIx::Tree::MaterializedPath;

    my $root = DBIx::Tree::MaterializedPath->new({
                                  dbh        => $dbh,
                                  table_name => 'my_movies_tree',

    # Add children to a node (assumes there is a "name" column
    # in the "my_movies_tree" table):
    my @children = $root->add_children([
                                        {name => 'Drama'},
                                        {name => 'Sci-Fi'},
                                        {name => 'Horror'},

    # Add a new child in front of any existing children,
    # instead of at the end of the list:
    my $child = $root->add_children_at_left([{name => 'Comedy'}]);

    # Locate a node (uses SQL::Abstract to query node metadata):
    my $sci_fi_node = $root->find(where => {name => {-like => 'Sci%'}});

    $sci_fi_node->add_child({name => 'The Andromeda Strain'});

    # Get children of a node:
    @children = $sci_fi_node->get_children();

    # Access arbitrary node metadata:
    print $children[0]->data->{name};    # 'The Andromeda Strain'

    # Walk tree (or node) descendants and operate on each node:
    my $descendants = $tree->get_descendants;


This module implements database storage for a "materialized path" parent/child tree.

Most methods (other than new()) can act on any node in the tree, including the root node. For documentation on additional methods see DBIx::Tree::MaterializedPath::Node.


This distribution was inspired by Dan Collis-Puro's DBIx::Tree::NestedSet modules but is implemented in a more object-oriented way.

Nested set trees are fast for typical tree queries (e.g. getting a node's parents, children or siblings), because those operations can typically be done with a single SQL statement. However, nested set trees are generally slow for modifications to the tree structure (e.g. adding, moving or deleting a node), because those operations typically require renumbering the hierarchy info for many affected nodes (often every node) due to a single modification. (This may not be an issue for you if you have data that is read often and updated very infrequently.)

This materialized path tree implementation does away with the integer "left" and "right" values that are stored with each node by nested-set trees, and instead uses a formatted representation of the path to the node (the "materialized path"), which is stored as a text string. It retains the speed for tree query operations, which can still typically be done with a single SQL statement, but generally requires far fewer updates to existing rows when modifications are made to the tree. This makes it better suited to situations where the tree is updated with any frequency.



    my $root = DBIx::Tree::MaterializedPath->new( $options_hashref )

new() initializes and returns a node pointing to the root of your tree.

Note: new() assumes that the database table containing the tree data already exists, it does not create the table for you. The table may be empty or may contain previously populated tree data. In addition to the required columns described below, the table may contain as many other columns as needed to store the metadata that corresponds to each node.

If the tree table does not contain a row with a path corresponding to the root node, a row for the root node will be inserted into the table. The new row will contain no metadata, so your application would need to call data() to add any required metadata.

new() accepts a hashref of arguments:



An active DBI handle as returned by DBI::connect().


Optional, defaults to "my_tree".

The name of the database table that contains the tree data.


Optional, defaults to "id".

The name of the database column that contains the unique ID for the node.

The ID is used internally as a "handle" to the row in the database corresponding to each node. (This column would typically be created in the database as e.g. a "sequence" or "serial" or "autoincrement" type.)


Optional, defaults to "path".

The name of the database column that contains the representation of the path from the root of the tree to the node.

Note that the path values which get stored in this column are generated by the code, and may not be particularly human-readable.


Optional, defaults to true.

If true, and no existing row is found in the database which matches the root node's path, will create a new row for the root node.

If false, and no existing row is found in the database which matches the root node's path, will croak.

Note that if a new root node row is created, it will not contain any values in any metadata fields. This means the database insert will fail if any of the corresponding columns in the database are required to be non-NULL.


Create a clone of an existing tree object.




Dan Collis-Puro's DBIx::Tree::NestedSet

An article about implementing materialized path and nested set trees:

An article about implementing nested set and static hierarchy trees:


Please report any bugs or feature requests to bug-dbix-tree-materializedpath at, or through the web interface at I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.


You can find documentation for this module with the perldoc command.

    perldoc DBIx::Tree::MaterializedPath

You can also look for information at:


Larry Leszczynski, <larryl at>


Copyright 2008 Larry Leszczynski, all rights reserved.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.