[Dbix-class] Multiple self join problem

[Rob Kinyon]

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.