Help with Many to Many Relationships

[Dave Cross-2]

I've having a brain freeze trying to implement a query in DBIC. I'm sure
I'm missing something obvious.

Here's the situation. Assume standard DBIC classes (created by
Schema::Loader).

Four tables - Director, Film, Actor, ActorInFilm

A Director directs many Films.
A Film has many Actors
An Actor is in Films

So the ActorInFilm table models the many-to-many relationship between
Actors and Films.

My problem is: given an actor and a director, get the list of films that
they worked on together. So the result will be a resultset containing
Film objects (or, alternatively, a list of Film objects).

I've been thinking about it too long and I can no longer think straight.
Even explaining the problem in this email hasn't helped.

Please tell me what I'm missing.

Cheers,

Dave...

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Ian Wells]

2009/6/17 Dave Cross <[hidden email]>:

> I've having a brain freeze trying to implement a query in DBIC. I'm sure I'm
> missing something obvious.
>
> Here's the situation. Assume standard DBIC classes (created by
> Schema::Loader).
>
> Four tables - Director, Film, Actor, ActorInFilm
>
> A Director directs many Films.
> A Film has many Actors
> An Actor is in Films
>
> So the ActorInFilm table models the many-to-many relationship between Actors
> and Films.
>
> My problem is: given an actor and a director, get the list of films that
> they worked on together. So the result will be a resultset containing Film
> objects (or, alternatively, a list of Film objects).

package Actor;

__PACKAGE->has_many('actorinfilms',
'ActorInFilm', { 'foreign.actor' => 'self.actor_id' });

package ActorInFilm;

__PACKAGE->belongs_to('film' => 'Film', { 'foreign.actor' => 'self.actor_id' });

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Jason Galea]

Hi Dave,

package DB::Film;

...

# has many actors
__PACKAGE__->has_many('film_actor_maps' => 'DB::FilmActorMap', 'product');
__PACKAGE__->many_to_many('actors' => 'film_actor_maps', 'actor');

__PACKAGE__->belongs_to('director' => 'DB::Director');

...

my $rs = $db->resultset('Film')->search(
  {
    director => 39,
    'film_actor_maps.actor' => 2,
  },
  {
    join => ['film_actor_maps'],
  }
);

cheers,

J

Dave Cross wrote:

> I've having a brain freeze trying to implement a query in DBIC. I'm
> sure I'm missing something obvious.
>
> Here's the situation. Assume standard DBIC classes (created by
> Schema::Loader).
>
> Four tables - Director, Film, Actor, ActorInFilm
>
> A Director directs many Films.
> A Film has many Actors
> An Actor is in Films
>
> So the ActorInFilm table models the many-to-many relationship between
> Actors and Films.
>
> My problem is: given an actor and a director, get the list of films
> that they worked on together. So the result will be a resultset
> containing Film objects (or, alternatively, a list of Film objects).
>
> I've been thinking about it too long and I can no longer think
> straight. Even explaining the problem in this email hasn't helped.
>
> Please tell me what I'm missing.
>
> Cheers,
>
> Dave...
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@...

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Ian Wells]

Bloody email client.  Sorry, will reply properly in a mo.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Ian Wells]

2009/6/17 Jason Galea <[hidden email]>:

> Hi Dave,
>
> package DB::Film;
>
> ...
>
> # has many actors
> __PACKAGE__->has_many('film_actor_maps' => 'DB::FilmActorMap', 'product');
> __PACKAGE__->many_to_many('actors' => 'film_actor_maps', 'actor');
>
> __PACKAGE__->belongs_to('director' => 'DB::Director');

I'd do it the other way around:

package DB::Actor;
...
# Has many films
__PACKAGE__->has_many('actor_film_maps' => 'DB::FilmActorMap', 'product');
__PACKAGE__->many_to_many('films' => 'actor_film_maps', 'film');

my $rs = $actor->films->search(director => 39);

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Dave Cross-2]

On 17/06/2009 13:12, Ian Wells wrote:

> 2009/6/17 Jason Galea<[hidden email]>:
>> Hi Dave,
>>
>> package DB::Film;
>>
>> ...
>>
>> # has many actors
>> __PACKAGE__->has_many('film_actor_maps' =>  'DB::FilmActorMap', 'product');
>> __PACKAGE__->many_to_many('actors' =>  'film_actor_maps', 'actor');
>>
>> __PACKAGE__->belongs_to('director' =>  'DB::Director');
>
>
> I'd do it the other way around:
>
> package DB::Actor;
> ...
> # Has many films
> __PACKAGE__->has_many('actor_film_maps' =>  'DB::FilmActorMap', 'product');
> __PACKAGE__->many_to_many('films' =>  'actor_film_maps', 'film');
>
> my $rs = $actor->films->search(director =>  39);

Thanks. That's exactly what I was looking for. I'd have got there myself
too - given another eight or ten hours of trying :-/

Cheers,

Dave...

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...