[Dbix-class] Q: prefetch has_many relation

[Peter Rabbitson]

Bernhard Graf wrote:
> 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>

Crap. Will fix.

> 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...

Crap x2

> 
>>> 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.

get_column on a resultset ($row->cds is such a resultset) is different from
get_column on an object. The resultset version must ensure there is only
one column selected in the result - thus the "impressive" subquery. By
the way I would like to see the generated SQL, there might be a problem
with the logic, especially if it seems to you that the query could have
been much simpler (and if this is indeed the case I will definitely fix it).

> 
> Changing this to
> 
>   for my $row ($rs->all) {
>     say join(',', map {$_->role_id} $row->user2roles->all);
>   }
> 
> fixed it indeed: no more additional queries. :)

Right, because you pull the role_id from the object, not from a resultset.

> 
> 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?
> 

Show the actual SQL - I'll be able to answer this.