[Dbix-class] Complex table joins
Richard Jones
ra.jones at dpw.clara.co.uk
Mon May 21 11:39:27 GMT 2007
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
More information about the Dbix-class
mailing list