Category Archives: databases

Using DBIx::Class::Tree::NestedSet

I’ve been struggling to get DBIx::Class::Tree::NestedSet working today because I found the documentation a bit sparse.

The problem is basically that the instructions assume that I’m very familiar with DBIx::Class, and that therefore I don’t need much detail.

After much trial and error I got it to work.

So these notes are aimed at someone who has got the basics of DBIx::Class running, with all the clever schema classes created (either manually or via something like dbicdump).

For this example, the tree is in a table called ‘treetable’, whose schema is defined in App::DB::Schema.  The table needs to have the following columns, in addition to the ones that you need for your application:

  root_id  integer,
  lft      integer NOT NULL,
  rgt      integer NOT NULL,
  level    integer NOT NULL,

This line:

__PACKAGE__->load_components(qw( Tree::NestedSet ... ));

needs to go near the bottom of, after the ‘DO NOT MODIFY THIS OR ANYTHING ABOVE!‘ line.  Note that the ellipsis (...) needs to be either deleted or replaced with any components that you’re already using.

These lines:

    root_column  => 'root_id',
    left_column  => 'lft',
    right_column => 'rgt',
    level_column => 'level',

go in, after the ‘DO NOT MODIFY THIS OR ANYTHING ABOVE!‘ line, and with the column names set to match the ones in your table.

That same file,, is the one that needs

use parent DBIx::Class::Tree::NestedSet;

near the top.

Once that all compiles cleanly, you can start accessing the tree table.

Start by creating the result set in the usual DBIC way:

my $rs = $schema->resultset('Treetable');

where $schema is your database handle.

Then you can start using the tree relationships and methods however you like.  Note that the documentation uses $node to refer to each node in the tree: each such $node is a DBIC object that can be used to access the fields from the corresponding database row, navigate to other nodes (children, siblings, etc.), or create new children or siblings.

For example,

my $first = $rs->first;  # get the first (or only) root node
my $desc_rs = $first->descendants;
while (my $desc = $desc_rs->next) { 
    print '**'x$desc->level, " id=", $desc->id, 
        ", field1=", $desc->name, "\n";

I hope that makes sense.