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

[Peter Rabbitson]

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=mc,molstat=mt,moldata=mdata)
> 
> 
> $attr->{join}='molstat';
> $attr->{prefetch}='molcfp';
> @molstats=split(/;/,$molstat);
> foreach my $i (@molstats) {
> my @split_molstats=split(/:/,$i);
>         my $a=$split_molstats[0];
>         my $b=$split_molstats[1];
>         $where->{"molstat." ."$a"}= [ -and => {'>=',int($b*$minval)},
> {'<=',int($b*$maxval)}];
> }
> foreach my $i (@molcfp_hfps) {
> my $num_hfp;
>         my @split_molcfp_hfps=split(/,/,$i);
>         if ($cont2 <10) {$num_hfp="hfp0" . $cont2;}
>         if ($cont2 >= 10) {$num_hfp="hfp" . $cont2;}
>         $n1 = unpack("B32", pack("N", $i));
>         $n1= ($n1=~ tr/1//);
>         for (my $j=1;$j<=16;$j++){
>                 $hfp[$j]=$molcfp_hfps[$j-1];
>                 }
>         $a_sum=$n1bits_q;
>         $i=int($i);
>         if ($n1>3) {$where-> {"molcfp." . "$num_hfp"}={"& $i >" ,'0'}};
>         $cont2++;
>         }
>         if ($n1bits_q>0) {
>         $where-> {'molcfp.n_h1bits'} =
> [-and=>{'>=',$punto_de_corte*$n1bits_q},{'<=',(1/$punto_de_corte)*$n1bits_q}];
>         }
> my $rs1 = $c->model('weight::Moldata')->search($where,$attr); (this line
> can vary according to the different alternatives I tried)
> 
> while ($cand = $rs1->next()) {

^^ $cand here is still an object of the 'Moldata' result class.

> $hfp01 = $cand-> get_column('hfp01');
>         $hfp02 = $cand-> get_column('hfp02');
>         $hfp03 = $cand-> get_column('hfp03');
>         $hfp04 = $cand-> get_column('hfp04');
>         $hfp05 = $cand-> get_column('hfp05');
>         $hfp06 = $cand-> get_column('hfp06');
>         $hfp07 = $cand-> get_column('hfp07');
>         $hfp08 = $cand-> get_column('hfp08');
>         $hfp09 = $cand-> get_column('hfp09');
>         $hfp10 = $cand-> get_column('hfp10');
>         $hfp11 = $cand-> get_column('hfp11');
>         $hfp12 = $cand-> get_column('hfp12');
>         $hfp13 = $cand-> get_column('hfp13');
>         $hfp14 = $cand-> get_column('hfp14');
>         $hfp15 = $cand-> get_column('hfp15');
>         $hfp16 = $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 = $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/Manual/Joining.pod#FETCHING-RELATED-DATA

Cheers