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

[Nilson Santos Figueiredo Junior]

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.

-Nilson Santos F. Jr.