NAME
DBIx::Class::MaterializedPath - efficiently retrieve and search trees
with DBIx::Class
VERSION
version 0.002001
SYNOPSIS
package A::Schema::Result::Category;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('category');
__PACKAGE__->load_components('MaterializedPath');
__PACKAGE__->add_columns(
id => {
data_type => 'int',
is_auto_increment => 1,
},
parent_id => {
data_type => 'int',
is_nullable => 1, # root
},
parent_path => {
data_type => 'varchar',
size => 256,
is_nullable => 1,
},
name => {
data_type => 'varchar',
size => 256,
},
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->belongs_to(
parent_category => 'A::Schema::Result::Category', 'parent_id'
);
__PACKAGE__->has_many(
child_categories => 'A::Schema::Result::Category', 'parent_id'
);
sub materialized_path_columns {
return {
parent => {
parent_column => 'parent_id',
parent_fk_column => 'id',
materialized_path_column => 'parent_path',
include_self_in_path => 1,
include_self_in_reverse_path => 1,
separator => '/',
parent_relationship => 'parent_category',
children_relationship => 'child_categories',
full_path => 'ancestors',
reverse_full_path => 'descendants',
},
}
}
1;
Elsewhere...
my $child_rows = $row->descendants;
or better yet
my $awesome_kids = $rs->search({ awesome => 1 })
->related_resultset('descendants');
DESCRIPTION
Materialized path
<
https://communities.bmc.com/communities/docs/DOC-9902> is a way to
store trees in relational databases that results in very efficient
retrieval, at the expense of space and more write-time queries.
This module makes using matpaths easy. The interface is somewhat
unusual, but the benefit is that it creates actual relationships for the
both directions of the tree, allowing you to use the powerful querying
DBIx::Class already gives you.
The first strange part of the interface is that the call to
"load_components" must come after the call to "table". The next strange
bit is that the way you define all of the metadata about your matpath is
by defining a subroutine called "materialized_path_columns". The
subroutine must return a hashref where the key is name of your path
(currently unused) and the value is the metadata for the path. Here are
the parts that need to be defined in the metadata:
* "parent_column" - the column that points directly to the parent row,
for example "parent_id".
* "parent_fk_column" - the column that "parent_column" points to, for
example "id".
* "materialized_path_column" - the column that contains the materialized
path. One thing to note here is that the width of the column defines
how deep your tree can be. For example if the number of digits for
most of your "id"'s is three, and your materialized path column is 255
characters wide, you can support a depth of something like 63 levels,
because each level in the tree is "q(/) . "$id"", so "255/4 ~~ 63". An
example for this might be "parent_materialized_path".
* "parent_relationship" - the direct relationship to your parent row
* "children_relationship" - the direct relationship to your child rows
* "full_path" - the name of the relationship that this materialized path
will define for all parents. For example, "ancestry".
* "reverse_full_path" - the name of the relationship that this
materialized path will define for all children. For example,
"descendants".
* "include_self_in_path" - (optional) I've found that it's often helpful
to include the current row in the full ancestry. For example if your
path is a for categorization system you probably want to turn this on.
* "include_self_in_reverse_path" - (optional) see above, but for chilren
instead of parents
* "separator" - (optional) defaults to "/". If you don't know what this
is for just ignore it.
AUTHOR
Arthur Axel "fREW" Schmidt <
[email protected]>
COPYRIGHT AND LICENSE
This software is copyright (c) 2014 by Arthur Axel "fREW" Schmidt.
This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.
