[Dbix-class] Q: prefetch has_many relation

[Bernhard Graf]

Am 08.04.2010 18:15, schrieb Peter Rabbitson:

>> Prefetching a has_many relation is not possible (says the manual).
> 
> o.O Please point out where does it say that so we can fix it.

http://search.cpan.org/~ribasushi/DBIx-Class/lib/DBIx/Class/ResultSet.pm#prefetch
<cite>
prefetch can be used with the following relationship types: belongs_to,
has_one (or if you're using add_relationship, any relationship declared
with an accessor type of 'single' or 'filter').
</cite>

I also tried to understand
http://search.cpan.org/~ribasushi/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using_joins_and_prefetch
but it seems contradictory, because here sometimes CDs seem to have many
artists

  My::Schema::CD->has_many(artists => 'My::Schema::Artist', 'artist_id');

  my $rs = $schema->resultset('CD')->search(
    {
      'artists.name' => 'Bob Marley'
    },
    {
      join     => 'artists',
      order_by => [qw/ artists.name /],
      prefetch => 'artists' # return artist data too!
    }
  );

  while (my $cd = $rs->next) {
    print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
  }

quite confusing...

>> How do I select all CDs of the artists of $artist_rs with one query?
> 
> my $new_rs = $artist_rs->search ({}, { prefetch => 'cds' });

I already noticed, that this builds a quite "impressive" SQL statement
with a sub-query - no idea why - but when I did

for my $row ($rs->all) {
  say join(', ', $row->cds->get_column('title')->all);
}

it still fetched each of the CDs again one by one.

Changing this to

  for my $row ($rs->all) {
    say join(',', map {$_->role_id} $row->user2roles->all);
  }

fixed it indeed: no more additional queries. :)

So actually it works - but I have no idea, why the SQL query has to be
so sophisticated... don't sub-queries usually slow down significantly?


Bernhard Graf