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. _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
