[Dbix-class] Nested joins + column subsets

[Matt S Trout]

On Tue, Nov 20, 2007 at 03:58:05PM -0800, Mark Blythe wrote:
> In trying what seemed like a simple operation today, I've either
> discovered a bug or documentation deficiency.  For the sake of
> brevity, I won't show full class definitions here, but let's say I
> have 3 simple table classes: A, B, C.  They each have a single primary
> key (a_id, b_id, c_id) with straightforward "belongs to" relationships
> between them:
> 
> A = (belongs to) => B = (belongs to) => C
> 
> A simple nested prefetch works fine:
> 
> $schema->resultset('A')->find($a_id, {
>    prefetch => { b => 'c' }
> });
> 
> However, if either B or C has a big blob or text column that I don't
> want to load, I may want to specify a subset of the columns.  Thus, I
> tried the following:
> 
> $schema->resultset('A')->find($a_id, {
>    columns => [ 'me.a_id', 'me.b_id', 'b.b_id', 'b.c_id', 'c.c_id', 'c.title' ],
>    join => { b => 'c' }
> });
> 
> I was careful to specify the keys on both sides of the relationships
> just to be safe.  This query does *not* work.  It dies with the error:
> 
> No such relationship "c"
> 
> I tried using 'select' rather than 'columns', but the same error
> occurred.  After much debugging, I discovered that the resultset's
> $self->{_attrs}{as} was being built differently than with the
> prefetch.  It showed the 'b' and 'c' relationships on the same level
> rather than nested, so later on when it died, it was looking for a "c"
> relationship in the A class, which of course there isn't.
> 
> I was able to fix the query by adding the "as" attribute and nesting
> the table prefixes there:
> 
> $schema->resultset('A')->find($a_id, {
>    select => [ 'me.a_id', 'me.b_id', 'b.b_id', 'b.c_id', 'c.c_id', 'c.title' ],
>    as => [ 'a_id', 'b_id', 'b.b_id', 'b.c_id', 'b.c.c_id', 'b.c.title' ],
>    join => { b => 'c' }
> });
> 
> If this is expected and necessary, I didn't come across it in the
> attribute docs under DBIx::Class::Resultset or in
> DBIx::Class::Manual::Cookbook.
> 
> So is this a bug or a feature?  BTW, I'm using v.08008

Feature. Both 'a' and 'b' could have a 'c' rel. This is how DBIC
disambiguates.

It's not documented because I don't think the people writing the docs did
this sort of selective prefetch; fancy whipping up a POD patch for us? :)

-- 
      Matt S Trout       Catalyst and DBIx::Class consulting and support -
   Technical Director      http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Christmas fun in collectable card game form -
                           http://www.shadowcat.co.uk/resources/2007_trading/