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

[Charlie Garrison]

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 }
);

And I am using the relationship in my controller:

my $descendants = $c->user->atr->descendants;

Which generates an error like:

"DBIx::Class::Relationship::Accessor::__ANON__(): Invalid rel 
cond val HASH(0x1e31984)

I haven't been able to find examples of more complex join 
criteria; I ended up with the above following the manual...

 From DBIC::Relationship::Base:
The condition needs to be an SQL::Abstract-style representation 
of the join between the tables.

 From SQL::Abstract:
You can use this same format to use other grouping functions, 
such as BETWEEN, SOME, and so forth. For example:
     my %where  = (
         user   => 'nwiger',
         completion_date => {
            -not_between => ['2002-10-01', '2003-02-06']
         }
     );

If anyone can show me where I'm going wrong and how I should 
create the join criteria (or some other way of approaching 
this), then I would appreciate it.

Thanks,
Charlie

-- 
    Charlie Garrison  <garrison at zeta.org.au>
    PO Box 141, Windsor, NSW 2756, Australia

O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
http://www.ietf.org/rfc/rfc1855.txt