[Dbix-class] Naming columns in nested joins

[Matt S Trout]

Jon wrote:
> Hi,
> 
> I'm doing a nested with three tables:
> 
> member: (has_many member_books mb)
> member_id
> 
> member_books (belongs to member, book)
> member_id
> book_id
> relation
> 
> book (has many member_books mb)
> book_id
> 
> 
> My query I'm running is "What (distinct) books does members who wants 
> the book the current member has have?"
> 
> 
> The way I see it this should be correct:
> MemberBooks->search(
>     {
>         'me.relation' => 'has',
>         'me.member_id' => $self->member_id
>     })->search_related('book')->search_related ('mb',
>               {
>                   'mb.relation' => 'wants',
>                   'mb.member_id' => {'!=', $self->member_id}
>               }
>     )->search_related ('member')->search_related('mb',
>     {
>         'mb.relation' => 'has',
>     },
>     { group_by => [' mb.book_id']}
>         );
> 
> But it results in:
> SELECT mb_2.member_id, mb_2.book_id, mb_2.language, mb_2.shape, 
> mb_2.relation FROM member_books me  JOIN book book ON ( book.book_id = 
> me.book_id ) LEFT JOIN member_books mb ON ( mb.book_id = book.book_id )  
> JOIN member member ON ( member.member_id = mb.member_id ) LEFT JOIN 
> member_books mb_2 ON ( mb_2.member_id = member.member_id ) WHERE ( ( ( 
> mb.relation = ? ) AND ( ( ( mb.member_id != ? AND mb.relation = ? ) AND 
> ( me.member_id = ? AND me.relation = ? ) ) ) ) ) GROUP BY mb.book_id 
> LIMIT 10: `has', `1', `wants', `1', `has'
> 
> Instead i have to change the last part to be:
> ->search_related('mb',
>     {
>         'mb_2.relation' => 'has',
>     },
>     { group_by => ['mb_2.book_id']}
>         );
> 
> 
> So my question is, am I doing something wrong here which makes DBIC 
> interpretate my query wrong, or am I supposed to keep track of the naming?

http://search.cpan.org/~jrobinson/DBIx-Class-0.06003/lib/DBIx/Class/ResultSet.pm#join

"If the same join is supplied twice, it will be aliased to <rel>_2 (and 
similarly for a third time)."

Basically, yes, you're supposed to keep track of it. Having a regular, 
simple rule for how relationships get aliased a second or third time 
means that you can always predict accurately what a given table in the 
query is going to be called, and means DBIC can always predirect it 
accurately as well. It takes a little bit of getting used to but once 
you have you'll find you don't have to think about it any more, and it 
ensures that DBIC can do all the magic it always does under the hood 
without trying to keep track of names its been given (which would be 
complex and potentially extremely fragile).