[Dbix-class] Multiple self join problem

[Terence Monteiro]

On Thu, Nov 12, 2009 at 08:29:46AM -0500, Rob Kinyon wrote:
> On Thu, Nov 12, 2009 at 08:08, Terence Monteiro <terence at deeproot.co.in> wrote:
> > I have 2 tables, one for soccer teams and one for players. I want to
> > find the team having players A, B and C.
> >
> > Schema::Team has many Schema::Player
> >
> > How could I accomplish the above? In SQL, one way would be:
> >
> > select teams.* from teams
> > inner join players p1 on p1.team = team.id
> > inner join players p2 on p2.team = team.id
> > inner join players p3 on p3.team = team.id
> > where p1.name = 'A'
> > and p2.name = 'B'
> > and p3.name = 'C';
> >
> > How could I do this using DBIx::Class?
> 
> $schema->model('teams')->search({
>     'players.name' => 'A',
>     'players2.name' => 'B',
>     'players3.name' => 'C',
> }, {
>     join => [ 'players', 'players', 'players' ],
> });
> 
> This is well documented.

Thanks, Rob. It worked with a slight modification:

$schema->model('teams')->search( {
    'players.name' => 'A',
    'players_2.name' => 'B',
    'players_3.name' => 'C',
}, {
    join => [ 'players', 'players', 'players' ],
} );

btw, where is it documented? I couldn't find it after googling or in
DBIx::Class::Manual::Cookbook.

-- 
Regards, Terence.