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

[Paula]

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*$minva=
l)},
> > {'<=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 l=
ine
> > 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/Manua=
l/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/20130122/f34=
8041e/attachment.htm