[Dbix-class] Re: Prefetch and join on different tables

[Paula]

Hello,
thank you all very much for the answers, I could figure it out and finally
solved the problem.
Very helpful!
Cheers



2013/1/22 Paula <mpmagarinos at gmail.com>

> Thank you for yours answer. I realized that I made a mistake in defining
> the relationships between tables. Because one each row in moldata
> corresponds to one row in molcfp and one in molstat (in fact they could be
> the same table). How does this affect the way of retrieving the resultset?
>
>
> 2013/1/22 Peter Rabbitson <rabbit+dbic at rabbit.us>
>
>> On Tue, Jan 22, 2013 at 02:46:40PM -0300, Paula wrote:
>> > OK this is the code: (in previous emails I used different names for the
>> > tables to simplify them, here I am using the real names in my code:
>> > molcfp=3Dmc,molstat=3Dmt,moldata=3Dmdata)
>> >
>> >
>> > $attr->{join}=3D'molstat';
>> > $attr->{prefetch}=3D'molcfp';
>> > @molstats=3Dsplit(/;/,$molstat);
>> > foreach my $i (@molstats) {
>> > my @split_molstats=3Dsplit(/:/,$i);
>> >         my $a=3D$split_molstats[0];
>> >         my $b=3D$split_molstats[1];
>> >         $where->{"molstat." ."$a"}=3D [ -and =3D> {'>=3D',int($b*$minv=
al)},
>> > {'<=3D',int($b*$maxval)}];
>> > }
>> > foreach my $i (@molcfp_hfps) {
>> > my $num_hfp;
>> >         my @split_molcfp_hfps=3Dsplit(/,/,$i);
>> >         if ($cont2 <10) {$num_hfp=3D"hfp0" . $cont2;}
>> >         if ($cont2 >=3D 10) {$num_hfp=3D"hfp" . $cont2;}
>> >         $n1 =3D unpack("B32", pack("N", $i));
>> >         $n1=3D ($n1=3D~ tr/1//);
>> >         for (my $j=3D1;$j<=3D16;$j++){
>> >                 $hfp[$j]=3D$molcfp_hfps[$j-1];
>> >                 }
>> >         $a_sum=3D$n1bits_q;
>> >         $i=3Dint($i);
>> >         if ($n1>3) {$where-> {"molcfp." . "$num_hfp"}=3D{"& $i >" ,'0'=
}};
>> >         $cont2++;
>> >         }
>> >         if ($n1bits_q>0) {
>> >         $where-> {'molcfp.n_h1bits'} =3D
>> >
>> [-and=3D>{'>=3D',$punto_de_corte*$n1bits_q},{'<=3D',(1/$punto_de_corte)*=
$n1bits_q}];
>> >         }
>> > my $rs1 =3D $c->model('weight::Moldata')->search($where,$attr); (this =
line
>> > can vary according to the different alternatives I tried)
>> >
>> > while ($cand =3D $rs1->next()) {
>>
>> ^^ $cand here is still an object of the 'Moldata' result class.
>>
>> > $hfp01 =3D $cand-> get_column('hfp01');
>> >         $hfp02 =3D $cand-> get_column('hfp02');
>> >         $hfp03 =3D $cand-> get_column('hfp03');
>> >         $hfp04 =3D $cand-> get_column('hfp04');
>> >         $hfp05 =3D $cand-> get_column('hfp05');
>> >         $hfp06 =3D $cand-> get_column('hfp06');
>> >         $hfp07 =3D $cand-> get_column('hfp07');
>> >         $hfp08 =3D $cand-> get_column('hfp08');
>> >         $hfp09 =3D $cand-> get_column('hfp09');
>> >         $hfp10 =3D $cand-> get_column('hfp10');
>> >         $hfp11 =3D $cand-> get_column('hfp11');
>> >         $hfp12 =3D $cand-> get_column('hfp12');
>> >         $hfp13 =3D $cand-> get_column('hfp13');
>> >         $hfp14 =3D $cand-> get_column('hfp14');
>> >         $hfp15 =3D $cand-> get_column('hfp15');
>> >         $hfp16 =3D $cand-> get_column('hfp16');
>>
>> ^^ these columns are part of the Molcfp result source. You can not
>> access them from the Moldata class (think for a minute how that would
>> work if both Moldata and Molcfp had columns named 'hfp01').
>>
>> Also Molcfp is a has-many. If you pay attention the amount of Moldata
>> "$cand"s you get back is equal to the amount of rows in Moldata, it is
>> not a cross-product of Moldata * Molstat * Molcfp.
>>
>> Instead what prefetch does is build you a hierarchy of objects. With or
>> without specifying it you can do:
>>
>> for $cand ... {
>>   my $molcfps =3D $cand->molcfp;
>>   for my $molcfp ($molcfps->all) {
>>     ...
>>   }
>> }
>>
>> The only thing that prefetch gives you is that the extra "drilling down"
>> the relationship tree does not fetch more data from the database.
>> Everything happens in one query. See [1] for more info and don't
>> hesitate to ask more questions if things aren't clear.
>>
>> [1]
>> https://metacpan.org/module/GETTY/DBIx-Class-0.08204/lib/DBIx/Class/Manu=
al/Joining.pod#FETCHING-RELATED-DATA
>>
>> Cheers
>>
>>
>> _______________________________________________
>> 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/dbix-class@lists.scsys.co.uk
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130123/71d=
ad3d1/attachment.htm