view release on metacpan or  search on metacpan

README  view on Meta::CPAN

    for the record with id "42" you have to do six queries against the
    database. If you maintain a materialized path you only have to do one.

    Consider our record "42" again. With its path 1/2/3/10/999/8/42 we can
    easily find all its parents–

     my $path = "1/2/3/10/999/8/42";
     my @ancestor_ids = split '/', $path;
     pop @ancestor_ids; # Remove the self id.
     my @ancestors = $result_source
                          ->search({ parent => { -in => \@ancestor_ids },
                                   { order_by => \"LENGTH(path)" });

    We can thank the great and powerful Ovid's co-worker Mark
    Morgan—<http://use.perl.org/~Ovid/journal/39460>—for the sorting
    solution for ensuring the proper order of ancestors is returned.

    See also *Trees in SQL: Nested Sets and Materialized Path*, Vadim
    Tropashko, <http://www.dbazine.com/oracle/or-articles/tropashko4>.

  CAVEAT
    This package requires your table has a single primary key and a method
    to look up a parent record by its single primary key.

METHODS
    [path method]
        Whatever column you set for your materialized path. In the
        "SYNOPSIS" code it is set to "path" to match the sample table
        definition. The default if you don't set one is "materialized_path".
        This will, of course, cause errors if there is no such column in the
        table.

    ancestors
        Searches on the materialized path ids excepting the object's own.
        This is generally cheap because it uses the path instead of
        recursion.

    get_root
        Returns the root object for a given record.

    grandchildren
        Return all children and grandchildren.

    node_depth
        Returns 1 for a record with no parent.

    root_node
    siblings
    max_depth
        Set this to assert a maximum tree depth. Default is 500.

    set_materialized_path
        Probably shouldn't mess with this. It's used by "insert" and
        "delete".

  OVERRIDDEN METHODS
    insert
        Sets the materialized path.

    update
        Updates which change the parent of a record necessarily cascade
        through all their children and grandchildren to recompute and set
        their new materialized paths. E.g., given this tree–

                          1
                          |
                          3
                         / \
                       12   8
                      /\    /\
                     5 13  7  4

        You get paths including 1/3/12/13 and 1/3/4. Let's say we change
        record 3's parent from 1 to 2–

                          2
                          |
                          3
                         / \
                       12   8
                      /\    /\
                     5 13  7  4

        The change is simple and it's obvious you have to update record 3
        but you just broke the materialized path for records 4, 5, 7, 8, 12,
        and 13. In a big tree you may have broken hundreds or even thousands
        of paths with a single parent change. So we have to process all
        descendants. Our example paths become 2/3/12/13 and 2/3/4. Again, it
        may seem trivial but it may be expensive depending on the tree's
        depth and breadth. This simplistic example will require three
        database reads—children of 3, children of 12, children of 8—and six
        updates—each of 4, 5, 7, 8, 12, and 13. This doesn't even count the
        original expense of finding and updating 3 itself. But the point
        here is that we should have a write seldom, read often situation and
        this up front expense may save exponentially with regards to ongoing
        query costs.

CAVEATS
    If your materialized path column is insufficiently large you're going to
    have problems. A "VARCHAR(255)" is only wide enough to support a tree
    which is 35 nodes deep if the average PK values are integers in the
    millions. This might be fine for your usage. Just be aware path tracking
    is not arbitrary, it's limited to the column's width.

TO DO
    Better documents; obviously.
    More tests; what else is new?
        One set with nothing changed: use default column names.

        One set with everything changed.

CODE REPOSITORY
    <http://github.com/pangyre/p5-dbicx-materializedpath>.

SEE ALSO
    DBIx::Class::Ordered, DBIx::Class.

  WHY NOT DBIx::Class::Ordered?
    There are data sets which have implicit, or even tacit,
    ordering—”position“ in DBIx::Class::Ordered parlance– in the data
    already. Published articles, for example, will be naturally ordered