[Dbix-class] Multi table join

[Matt S Trout]

On Sat, Dec 31, 2005 at 12:01:39PM +0100, Jon Molin wrote:
> In the DBIx::Class::Manual::Cookbook there's an example with an author/book
> seems like the database is somewhat like this:
> 
> author
> authID int
> first_name varchar
> last_name varchar
> 
> book2author
> authID int
> bookID int
> 
> book
> bookID int
> title  varchar
> ...
> 
> 
> Now, if I know the title starts with foo% but want to limit my result to
> authors bar% I'd go like this in sql:
> 
> SELECT a.*, b.* FROM book b, author a, book2author a2b
>   WHERE (b.title LIKE 'foo%' AND a.first_name LIKE 'bar%') AND
>         (b.bookID = a2b.bookID AND a.authID = a2b.authID
> 
> I've two questions regarding this. First, how to do it? I can't find much
> documentation and in the doc I've found haven't I found a way to do a multi
> table join. Secondly where would be a sane place to put a function like
> this? Would Book2Author.pm be the correct place since it's the table
> connecting them?

my $rs = Book2Author->search({
           'book.title' => { 'like', 'foo%' },
           'author.first_name' => { 'like', 'bar%' } },
         { prefetch => [ qw/book author/ ] });

should do what you want, since prefetch automatically fills out the 'join'
attribute to include the tables.

I'm afraid the docs are still fairly light on join stuff; the join.tl in t/run
is probably the best source of examples. Contributions would be very much
welcome :)

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +