[Dbix-class] Setup relationship for 'modified preorder tree traversal' structure

[Matt S Trout]

On Mon, Jun 25, 2007 at 05:59:29AM +1000, Charlie Garrison wrote:
> Good morning,
> 
> I am using 'modified preorder tree traversal' for one of my 
> tables and I'm having trouble working out how to create the 
> relationship for descendant records.
> 
> The table looks like (simplified):
> 
> CREATE TABLE access_tree (
>   atr_key int(10) unsigned NOT NULL auto_increment,
>   participant_key int(10) unsigned DEFAULT '0' NOT NULL,
>   lft bigint(21) unsigned DEFAULT '0' NOT NULL,
>   rgt bigint(21) unsigned DEFAULT '0' NOT NULL,
>   PRIMARY KEY (atr_key),
>   KEY participant_key (participant_key),
>   KEY lft (lft),
>   KEY rgt (rgt)
> );
> 
> My model looks like this (simplified):
> 
> package RPDB::AccessTree;
> 
> use base 'DBIx::Class';
> use strict;
> use warnings;
> 
> __PACKAGE__->load_components(qw/ Core /);
> __PACKAGE__->table('access_tree');
> __PACKAGE__->add_columns(qw/ atr_key participant_key lft rgt /);
> __PACKAGE__->set_primary_key('atr_key');
> 
> __PACKAGE__->belongs_to(user => 'RPDB::User', 'atr_key'); # 
> reverse rel setup in RPDB::User
> __PACKAGE__->belongs_to(participant => 'RPDB::Participant', 
> 'participant_key');
> __PACKAGE__->has_many(descendants => 'RPDB::AccessTree',
>     { 'foreign.lft' => { -between => [ 'self.lft', 'self.rgt' ] 
> } },
>     { cascade_delete => 0 }
> );

Not handled yet. Try

sub descendants {
  my $self = shift;
  $self->result_source->resultset->search({
    'lft' => { -between => [ $self->lft, $self->rgt ] }
  });
}

for the moment.

If you'd be interested, I'd dearly love to see a nested set implementation
for DBIx::Class::Tree and would be more than happy to help out if you get
stuck anywhere in doing so.

There's actually one already built into MojoMojo but the svn for that's
currently midway through a migration - I'll reply again to this thread
when that's done.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/             http://www.shadowcatsystems.co.uk/