WG: [Dbix-class] Oracle Prefetch / Join

[SHAD0WRUNNER at gmx.de]

>> Hello again,
>>
>> I tried this example with Oracle:
>>
>> Tables CDS -> Tracks
>>
>> #1. Package:
>> #############
>>
>> package Schema::CD;
>>
>> use base qw/DBIx::Class/;
>>
>> __PACKAGE__->load_components(qw/ PK::Auto Core /);
>>
>> __PACKAGE__->table('cds');
>>
>> __PACKAGE__->add_columns(
>>           id => {
>>                data_type    => "varchar2",
>>                size         => "64",
>>                prec         => "0",
>>                scale        => "0",
>>                is_nullable  => "n",
>>           }
>> );
>>
>>
>> __PACKAGE__->set_primary_key(qw/ id / );
>>
>> __PACKAGE__->has_many('tracks' => 'Schema::Track', 'cd_id');
>>
>> 1;
>>
>> #2. Package:
>> #############
>>
>> package Schema::Track;
>>
>> use base qw/DBIx::Class/;
>>
>> __PACKAGE__->load_components(qw/ PK::Auto Core /);
>>
>> __PACKAGE__->table('tracks');
>>
>> __PACKAGE__->add_columns(
>>           id => {
>>                data_type    => "varchar2",
>>                size         => "64",
>>                prec         => "0",
>>                scale        => "0",
>>                is_nullable  => "n",
>>           },
>>          cd_id => {
>>                data_type    => "varchar2",
>>                size         => "64",
>>           }
>> );
>>
>>
>> __PACKAGE__->set_primary_key(qw/ id / );
>>
>> __PACKAGE__->belongs_to('cd' => 'Schema::CD', 'cd_id');
>>
>> 1;
>>
>>
>> #test.pl
>> #########
>> ....
>>
>> $rs = $schema->resultset('CD')->search( { id => "test" },
>>                                        {prefetch => "tracks"}
>>                                       );
>>
>> $i=1;
>> while($rs->next){
>>  print "$i ...\n";
>>  $i++;
>> }
>>
>>
>>
>> If there is 1 recordset in table CDS and 2 related recordsets in 
>> TRACKS I would expect:
>>
>> 1 ...
>>
>> but i got:
>>
>> 1 ...
>> 2 ...
>> 3 ...
>>
>> Is this the expected behavior?
>>
>> The same effect I got with join.


>No. Care to rurn on DBIC_TRACE and give us the sql you got?

>Jess

Sorry, prefetch works as aspected,
But join doesn't. 

I changed 2 things. The primary keys are numbers and 
I used the following code:

$rs = $schema->resultset('CD')->search( { "tracks.cd_id" => 1 },
                                        {join => "tracks"}
                                       );
while($obj=$rs->next){

  print "$i ...\n";
  foreach my $track ($obj->tracks()){
    print "Track ...\n";
  }
  $i++;

}


I got the following output:

SELECT me.id FROM cds me LEFT JOIN tracks tracks ON ( tracks.cd_id = me.id ):
1 ...
SELECT me.id, me.cd_id FROM tracks me WHERE ( me.cd_id = ? ): '1'
Track ...
Track ...
Track ...
2 ...
SELECT me.id, me.cd_id FROM tracks me WHERE ( me.cd_id = ? ): '1'
Track ...
Track ...
Track ...
3 ...
SELECT me.id, me.cd_id FROM tracks me WHERE ( me.cd_id = ? ): '1'
Track ...
Track ...
Track ...

I expected only 1 recordset as return value but got 
3. Well, {join     => "tracks",
          distinct => 1
         }

solves the problem.

Dieter



-- 
FastJack

"Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail