[Dbix-class] Re: Join behavior in "prefetch" vs "join"

[Peter Rabbitson]

Nilson Santos Figueiredo Junior wrote:
> On Fri, Jan 9, 2009 at 3:09 PM, Nilson Santos Figueiredo Junior
> <acid06 at gmail.com> wrote:
>> So - is prefetch really expected to work like that?
>> I can explain better if this message isn't clear enough and a test
>> case if it seems to be a bug.
> 
> Just an update, with some quick code snippets which might be able to
> better explain the issue.
> 
> These are the relationship definitions:
> 
>   package My::Schema::Foo;
>   __PACKAGE__->has_many( 'bars', 'My::Schema::Bar', { 'foreign.foo_id'
> => 'self.id' } );
> 
>   package My::Schema::Bar;
>   __PACKAGE__->has_many('quuxes', 'My::Schema::Quux', {
> 'foreign.bar_id' => 'self.id' } );
> 
>   package My::Schema::Quux;
>   __PACKAGE__->belongs_to('parent', 'My::Schema::Parent', { id =>
> 'parent_id' } );
> 
> Then, some sample queries:
> 
>   my $prefetch_rs = $schema->resultset('Foo')->search( undef, {
>       prefetch => { bars => { quuxes => 'parent' } }
>   } );
> 
>   my $join_rs = $schema->resultset('Foo')->search( undef, {
>       join => { bars => { quuxes => 'parent' } }
>   } );
> 
> In this case, $prefetch_rs will emit something like:
> 
>   LEFT JOIN bar LEFT JOIN quux JOIN parent
> 
> While $join_rs will emit something like:
> 
>     LEFT JOIN bar LEFT JOIN quux LEFT JOIN parent
> 
> The expected behavior is better matched by the query emitted by
> $join_rs (an exact match would be using nested joins, so that quux
> still must have a parent, but that wouldn't exclude the entire 'bar'
> row from the resultset).
> 
> So, I hope this explains it better.
> 

Looks like a genuine bug. Could you please submit a text patch
illustrating the issue against the current trunk[1]. t/77prefetch.t
would be a good place (use the existing infrastructure and tests as
reference).

[1] http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk