[Dbix-class] Many-to-many with attributes

[Matt S Trout]

On Sat, Nov 26, 2005 at 12:06:19PM +0000, Jonathan Tweed wrote:
> Hi List
> 
> I'm trying to create a many-to-many mapping with attributes on the  
> join table. I've got the many-to-many bit working, but can't figure  
> out how to get at the attributes. My current attempt is as follows:
> 
> package DB::List;
> 
> use strict;
> use warnings;
> use base 'DB';
> 
> __PACKAGE__->table('list');
> __PACKAGE__->add_columns(qw/id client name internal/);
> __PACKAGE__->set_primary_key('id');
> __PACKAGE__->belongs_to('client' => 'DB::Client');
> __PACKAGE__->has_many('list_subscribers' => 'DB::ListSubscriber');
> 
> sub subscribers {
>     my ($self) = @_;
> 
     return $self->list_subscribers({},
              { order_by => 'id', prefetch => 'subscriber' });
> }
> 
> 1;
> 
> package DB::ListSubscriber;
> 
> use strict;
> use warnings;
> use base 'DB';
> 
> __PACKAGE__->table('list_subscriber');
> __PACKAGE__->add_columns(qw/list subscriber subscribed unsubscribed/);
> __PACKAGE__->set_primary_key(qw/list subscriber/);
> __PACKAGE__->belongs_to('list' => 'DB::List');

 __PACKAGE__->belongs_to('subscriber' => 'DB::Subscriber', undef,
                            { proxy => [ qw/email id/ ] });

> 
> 1;
> 
> package DB::Subscriber;
> 
> use strict;
> use warnings;
> use base 'DB';
> 
> __PACKAGE__->table('subscriber');
> __PACKAGE__->add_columns(qw/id email/);
> __PACKAGE__->set_primary_key('id');
> __PACKAGE__->has_many('list_subscribers' => 'DB::ListSubscriber');
> 
> sub list {
>     my ($self) = @_;
> 
>     return DB::List->search(
>         { 'subscriber.id' => $self->id },
>         { join => { 'list_subscribers' => 'subscriber' } });
> }
> 
> 1;
> 
> When I do $list->subscribers, the following SQL statement is produced:
> 
> SELECT me.id, me.email, list_subscribers.subscribed,  
> list_subscribers.unsubscribed FROM subscriber me LEFT JOIN  
> list_subscriber list_subscribers ON ( list_subscribers.subscriber =  
> me.id )  JOIN list list ON ( list.id = list_subscribers.list ) WHERE  
> ( list.id = ? ) ORDER BY email
> 
> which when I run it againt the database returns exactly what I want.  
> However a $list->subscribers->next produces

The above changes means that you'll get a ListSubscriber object back but
$ls->email and ->id will be proxied through to the Subscriber object (which
will already have been grabbed by the prefetch). You can still call
$ls->subscriber->email if required though.

> Thanks for any help, I'm an ex Class::DBI user but very new to  
> DBIx::Class and so far I'm impressed. When I get this working I'll  
> add an example to the wiki.

That'd be much appreciated.

-- 
     Matt S Trout       Specialists in Perl consulting, web development, and
  Technical Director    UNIX/Linux systems architecture and automation. Mail
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

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