[Dbix-class] Complex table joins

[Richard Jones]

I've got table joins working OK where base table_1 (me) contains foreign 
keys to 3 other tables (table_2, table_3 & table_4).  So the 'base' (me) 
table_1 contains 3 'belongs_to' relationships, and tables_ 2, 3 & 4 each 
contain a 'has_many' relationship with the base table. Then:

my $rs = $c->model('Schema::Foo')->search( $where_href, # WHERE clause
  {
    select => qw( table1.id table2.field table3.something_else etc ),
    join   => [ qw/table_2 table_3 table_4/ ],
  },
)

And this works OK.

But what if table_4 itself has a foreign key to table_5. The manual sql 
construction is:
SELECT many_fields_from_other_tables, t5.bar
FROM table_1 me
     LEFT JOIN table_2 t2 on me.t2_id = t2.id
     LEFT JOIN table_3 t3 on me.t3_id = t3.id
     LEFT JOIN table_4 t4 on me.t4_id = t4.id
     LEFT JOIN table_5 t5 ON ( t5.foo = t4.foo )
WHERE ( $where_clause )

* foo is not table_5's primary key (though I could make it so if it 
would help).

No matter how I define table_5 in the join arrayref, I get the same 
error message:

"DBIx::Class::ResultSet::pager(): No such relationship table_5 at ....".

Based on the Cookbook 'Multi-step joins' section I've tried various 
variations of:
  join => [ qw/table_2 table_3 table_4 table_5/ ],
  join => [ qw/table_2 table_3/], { 'table_4' => 'table_5' } ],
  join => [ qw/table_2 table_3/], table_1 => { 'table_4' => 'table_5' } ],
  etc.

I'm not sure if the problem is in the controllers join arrayref, or in 
the Schema definitions (same belongs_to and has_many as for other 4 
tables). I think the reason the Cookbook example doesn't work in my 
setup is because I have a more complex table join relationship.

-- 
Richard Jones