Re: [Dbix-class] Re: Prefetch and join on different tables
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
> 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
>
>> 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
>>
>>
>> ___
>> 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
>>
>
>
___
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
Re: [Dbix-class] Re: Prefetch and join on different tables
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
> 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
>
>
> ___
> 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
>
___
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
Re: [Dbix-class] Re: Prefetch and join on different tables
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()) {
$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');
$n_h1bits_base = $cand-> get_column('n_h1bits');
$mol_id_sdf_cand[$nromol] = $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 => "INT", default_value => "", is_nullable => 0, size => 11 },
...
...
);
__PACKAGE__->set_primary_key("mol_id");
__PACKAGE__->has_many(
'molcfp', 'Molcfp', { 'foreign.mol_id' => 'self.mol_id'} );
__PACKAGE__->has_many(
'molstat', 'Molstat', { 'foreign.mol_id' => '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 => "INT", default_value => 0, is_nullable => 0, size => 11 },
"hfp01",
{ data_type => "INT", default_value => "", is_nullable => 0, size => 11 },
...
...
__PACKAGE__->set_primary_key("mol_id");
__PACKAGE__->belongs_to(
'moldata', 'Moldata', { 'mol_id' => 'mol_id'});
__PACKAGE__->has_many(
'molstat', 'Molstat', { 'foreign.mol_id' => '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 => "INT", default_value => 0, is_nullable => 0, size => 11 },
...
...
__PACKAGE__->set_primary_key("mol_id");
__PACKAGE__->belongs_to(
'moldata', 'Moldata', { 'foreign.mol_id' => 'self.mol_id'});
__PACKAGE__->has_many(
'molcfp', 'Molcfp', { 'foreign.mol_id' => 'self.mol_id'} );
Thanks!
2013/1/22 Peter Rabbitson
> On Tue, Jan 22, 2013 at 11:23:42AM -0300, Paula wrote:
> > I tried this, and in this case I get:
> >
> >
Re: [Dbix-class] Re: Prefetch and join on different tables
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
2013/1/21 Charlie Garrison
> Good afternoon,
>
> On 21/01/13 at 5:39 PM -0300, Paula wrote:
>
> >2013/1/21 Dagfinn Ilmari Mannsåker
> >
> >> ->search($where, { %{$attr}, prefetch => 'mc', join => 'mt' });
> >
> >
> >I have just tried that, and again I get:
> >[error] DBIx::Class::Row::get_column(): No such column 'h1'
> >
> >It seems the order is important, the last argument (prefetch or join) is
> >the only one that is executed...
>
> Correct, since standard perl behaviour says there is only one key in a
> hash.
>
> Assuming $attr->{join} is currently a string (if already arrayref then
> just push onto it):
>
> $attr->{join} = [$attr->{join}, 'mt'];
> $attr->{prefetch} = [$attr->{prefetch}, 'mc'];
> search($where, $attr});
>
>
> Charlie
>
> --
>Ꮚ Charlie Garrison ♊
>
> O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
> 〠 http://www.ietf.org/rfc/rfc1855.txt
>
>
> ___
> 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
>
___
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
Re: [Dbix-class] Re: Prefetch and join on different tables
2013/1/21 Dagfinn Ilmari Mannsåker
> ->search($where, { %{$attr}, prefetch => 'mc', join => 'mt' });
I have just tried that, and again I get:
[error] DBIx::Class::Row::get_column(): No such column 'h1'
It seems the order is important, the last argument (prefetch or join) is
the only one that is executed...
___
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
Re: [Dbix-class] Prefetch and join on different tables
I really don't understand why this happens...
If the search is:
my $rs1 = $c->model('weight::Mdata')->search($where,$attr,{prefetch =>
'mc'},{join =>'mt'});
The DBIC TRACE shows that the join occurs between mdata and mt, and I get
the following error:
[error] DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'mc.h1' in 'where clause'
Inversely, if the search is:
my $rs1 = $c->model('weight::Mdata')->search($where,$attr,{join
=>'mt'},{prefetch => 'mc'});
The DBIC TRACE shows that the join (and the prefetch) occurs between mdata
and mc, and I get the following error:
[error] DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'mt.n_C' in 'where clause'
It seems as the prefetch 'erases' the join and viceversa...
2013/1/21 Paula
> Hi! Thank you for your answer. Unfortunately neither of the 2
> possibilities work. In both cases I get the same error:
> [error] DBIx::Class::Row::get_column(): No such column 'h1'
>
>
> 2013/1/18 Patrick Meidl
>
>> On Fri, Jan 18 2013, mpm wrote:
>>
>> > push @{ $attr->{join} }, 'mt';
>> > push @{ $attr->{join} }, 'mc';
>> > my $rs1 = $c->model('weight::Mdata')->search( $where, $attr,{prefetch =>
>> > 'mc'});
>>
>> I think your curly braces are misplaced. this should do what you want:
>>
>> my $rs1 = $c->model('weight::Mdata')->search($where,
>> { %$attr, prefetch => 'mc' });
>>
>> or alternatively:
>>
>> push @{ $attr->{join} }, 'mt';
>> push @{ $attr->{join} }, 'mc';
>> $attr->{prefetch} = 'mc';
>> my $rs1 = $c->model('weight::Mdata')->search($where, $attr);
>>
>>
>> HTH
>>
>> patrick
>>
>> --
>> Patrick Meidl, Mag.
>> Senior Expert Software Engineering
>>
>> IST - Institute of Science and Technology Austria
>> Am Campus 1
>> A-3400 Klosterneuburg, Austria
>>
>> R 03.EG.003
>> T +43 2243 9000 1063
>> E pme...@ist.ac.at
>> W http://www.ist.ac.at/
>>
>>
>> ___
>> 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
>>
>
>
___
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
Re: [Dbix-class] Prefetch and join on different tables
Hi! Thank you for your answer. Unfortunately neither of the 2 possibilities
work. In both cases I get the same error:
[error] DBIx::Class::Row::get_column(): No such column 'h1'
2013/1/18 Patrick Meidl
> On Fri, Jan 18 2013, mpm wrote:
>
> > push @{ $attr->{join} }, 'mt';
> > push @{ $attr->{join} }, 'mc';
> > my $rs1 = $c->model('weight::Mdata')->search( $where, $attr,{prefetch =>
> > 'mc'});
>
> I think your curly braces are misplaced. this should do what you want:
>
> my $rs1 = $c->model('weight::Mdata')->search($where,
> { %$attr, prefetch => 'mc' });
>
> or alternatively:
>
> push @{ $attr->{join} }, 'mt';
> push @{ $attr->{join} }, 'mc';
> $attr->{prefetch} = 'mc';
> my $rs1 = $c->model('weight::Mdata')->search($where, $attr);
>
>
> HTH
>
> patrick
>
> --
> Patrick Meidl, Mag.
> Senior Expert Software Engineering
>
> IST - Institute of Science and Technology Austria
> Am Campus 1
> A-3400 Klosterneuburg, Austria
>
> R 03.EG.003
> T +43 2243 9000 1063
> E pme...@ist.ac.at
> W http://www.ist.ac.at/
>
>
> ___
> 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
>
___
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
Re: [Dbix-class] Error with +columns
thanks! the result of this was 0.08195 2012/12/15 Dwalu Khasu > perl -e 'use DBIx::Class; print "$DBIx::Class::VERSION\n"' ___ 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
Re: [Dbix-class] Error with +columns
I don't really know how to check the version 2012/12/14 Peter Rabbitson > On Fri, Dec 14, 2012 at 12:48:22PM -0800, mpm wrote: > > I am getting an error, that was not happening before: > > What version were you using before, and what version are you using now. > > ___ > 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 > ___ 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
