[Dbix-class] Q: prefetch has_many relation

[Bernhard Graf]

Am 08.04.2010 22:39, schrieb Peter Rabbitson:

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

The actual SQL is rather big, so I'm using the well known KISS CD
database example.

Meanwhile I found out, that the resultset attribute "rows" is
"responsible" for switching to the big SQL code:

Without "rows":

my $artist_rs = $schema->resultset('Artist');

my $new_rs = $artist_rs->search(
    undef, {
        prefetch => 'cds',
    }
);

while (my $artist = $new_rs->next) {
    say $artist->name;
    my $cd_rs = $artist->cds;
    while (my $cd = $cd_rs->next) {
        say "\t", $cd->title;
    }
}

SELECT me.id, me.name, cds.id, cds.artist_id, cds.title
FROM artist me
LEFT JOIN cd cds ON cds.artist_id = me.id
ORDER BY cds.artist_id


With "rows":

my $new_rs = $artist_rs->search(
    undef, {
        prefetch => 'cds',
        rows => 2,
    }
);

[...]


SELECT me.id, me.name, cds.id, cds.artist_id, cds.title
FROM (
  SELECT me.id, me.name
  FROM artist me
  GROUP BY me.id, me.name
  LIMIT 2
) me
LEFT JOIN cd cds ON cds.artist_id = me.id
ORDER BY cds.artist_id


I could send you the complete archive (which is similar to manual page
DBIx::Class::Manual::Example) with the test db and scripts if it helps.


Bernhard Graf