[Dbix-class] Complex table joins

[SHAD0WRUNNER at gmx.de]

-------- Original-Nachricht --------
Datum: Mon, 21 May 2007 11:39:27 +0100
Von: Richard Jones <ra.jones at dpw.clara.co.uk>
An: dbix-class at lists.rawmode.org
Betreff: [Dbix-class] Complex table joins

> 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 think 

join => [{ 'table_4' => 'table_5' }, qw/table_2 table_3/]

should work.

Greetings
Dieter


> 
> 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 
> 
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive:
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/

-- 
FastJack

Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer