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

[Paula]

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*$minval)},
{'<=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)*$n1=
bits_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()) {
$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');
        $n_h1bits_base =3D $cand-> get_column('n_h1bits');
        $mol_id_sdf_cand[$nromol] =3D $cand-> get_column('mol_id');
        $nromol++;
}

-----
These are the 3 tables involved (moldata, molcfp, molstat)

package weight::Moldata;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("moldata");
__PACKAGE__->add_columns(
  "mol_id",
  { data_type =3D> "INT", default_value =3D> "", is_nullable =3D> 0, size =
=3D> 11 },
...
...
);
__PACKAGE__->set_primary_key("mol_id");
__PACKAGE__->has_many(
'molcfp', 'Molcfp', { 'foreign.mol_id' =3D> 'self.mol_id'} );
__PACKAGE__->has_many(
'molstat', 'Molstat', { 'foreign.mol_id' =3D> 'self.mol_id'} );


package weight::Molcfp;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("molcfp");
__PACKAGE__->add_columns(
  "mol_id",
  { data_type =3D> "INT", default_value =3D> 0, is_nullable =3D> 0, size =
=3D> 11 },
"hfp01",
  { data_type =3D> "INT", default_value =3D> "", is_nullable =3D> 0, size =
=3D> 11 },
...
...
__PACKAGE__->set_primary_key("mol_id");
__PACKAGE__->belongs_to(
'moldata', 'Moldata', { 'mol_id' =3D> 'mol_id'});
__PACKAGE__->has_many(
'molstat', 'Molstat', { 'foreign.mol_id' =3D> 'self.mol_id'} );

package weight::Molstat;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("molstat");
__PACKAGE__->add_columns(
  "mol_id",
  { data_type =3D> "INT", default_value =3D> 0, is_nullable =3D> 0, size =
=3D> 11 },
...
...
__PACKAGE__->set_primary_key("mol_id");
__PACKAGE__->belongs_to(
'moldata', 'Moldata', { 'foreign.mol_id' =3D> 'self.mol_id'});
__PACKAGE__->has_many(
'molcfp', 'Molcfp', { 'foreign.mol_id' =3D> 'self.mol_id'} );


Thanks!


2013/1/22 Peter Rabbitson <rabbit+dbic at rabbit.us>

> On Tue, Jan 22, 2013 at 11:23:42AM -0300, Paula wrote:
> > I tried this, and in this case I get:
> >
> > [error] DBIx::Class::Row::get_column(): No such column 'h1'
> >
> > so in this case it is not executing prefetch
>
> You are missing something simple, yet non-obvious. Please supply the
> *entire* search() call, and the *entire* definition of the Result clases
> involved (the stuff that has ->table, ->add_columns, etc).
>
> Then we will be able to precisely explain where your expectations do not
> meet with the reality of how DBIC operates (and perhaps you will even
> help us fix the docs to make it clearer).
>
> 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/969=
19c8e/attachment.htm