DBIx::Class::MaterializedPath - efficiently retrieve and search trees with DBIx::Class
version 0.002001
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');
Materialized path 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:
load_components
table
materialized_path_columns
parent_column - the column that points directly to the parent row, for example parent_id.
parent_column
parent_id
parent_fk_column - the column that parent_column points to, for example id.
parent_fk_column
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.
materialized_path_column
q(/) . "$id"
255/4 ~~ 63
parent_materialized_path
parent_relationship - the direct relationship to your parent row
parent_relationship
children_relationship - the direct relationship to your child rows
children_relationship
full_path - the name of the relationship that this materialized path will define for all parents. For example, ancestry.
full_path
ancestry
reverse_full_path - the name of the relationship that this materialized path will define for all children. For example, descendants.
reverse_full_path
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_path
include_self_in_reverse_path - (optional) see above, but for chilren instead of parents
include_self_in_reverse_path
separator - (optional) defaults to /. If you don't know what this is for just ignore it.
separator
/
Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
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.
To install DBIx::Class::MaterializedPath, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Class::MaterializedPath
CPAN shell
perl -MCPAN -e shell install DBIx::Class::MaterializedPath
For more information on module installation, please visit the detailed CPAN module installation guide.