[Dbix-class] Multiple self join problem

iain iainhubbard at googlemail.com
Fri Nov 13 11:45:28 GMT 2009


Terence Monteiro wrote:
> 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.
>
>   
http://search.cpan.org/~ribasushi/DBIx-Class-0.08112/lib/DBIx/Class/Manual/Joining.pod

google "joining to the same table dbix::class" its the top link

Iain.



More information about the DBIx-Class mailing list