<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello,<br>
<br>
I did a few more test and here are the results. If I run the query
inside the sqlite3 shell, the result I get is :<br>
<blockquote type="cite">SQLite version 3.4.2<br>
Enter ".help" for instructions<br>
sqlite> SELECT me.location_id, me.start, me.end, me.accession,
me.aligment_id, aligment.aligment_id, aligment.strand,
aligment.reference_sequence_id, aligment.score, aligment.distant_id,
aligment.distant_table FROM location me JOIN aligment aligment ON (
aligment.aligment_id = me.aligment_id ) WHERE ( ( ( start*strand <
45622542 ) AND ( me.aligment_id = 507781 ) ) ) ORDER BY (start*strand)
DESC;<br>
2884873|45620932|45620997||507781|507781|1|27|1.0|742163|transcript<br>
2884874|45587961|45588122||507781|507781|1|27|1.0|742163|transcript<br>
2884875|45567656|45567829||507781|507781|1|27|1.0|742163|transcript<br>
2884876|45513662|45513766||507781|507781|1|27|1.0|742163|transcript<br>
2884877|45507578|45507734||507781|507781|1|27|1.0|742163|transcript<br>
2884878|45498308|45498672||507781|507781|1|27|1.0|742163|transcript<br>
2884879|45404376|45404499||507781|507781|1|27|1.0|742163|transcript<br>
2884880|45404032|45404175||507781|507781|1|27|1.0|742163|transcript<br>
</blockquote>
<br>
The same in query in the mysql shell:<br>
<blockquote type="cite">mysql> SELECT me.location_id, me.start,
me.end, me.accession, me.aligment_id, aligment.aligment_id,
aligment.strand, aligment.reference_sequence_id, aligment.score,
aligment.distant_id, aligment.distant_table FROM location me JOIN
aligment aligment ON ( aligment.aligment_id = me.aligment_id ) WHERE (
( ( start*strand < 45622542 ) AND ( me.aligment_id = 507781 ) ) )
ORDER BY (start*strand) DESC;<br>
+-------------+----------+----------+-----------+-------------+-------------+--------+-----------------------+-------+------------+---------------+<br>
| location_id | start | end | accession | aligment_id |
aligment_id | strand | reference_sequence_id | score | distant_id |
distant_table |<br>
+-------------+----------+----------+-----------+-------------+-------------+--------+-----------------------+-------+------------+---------------+<br>
| 2884873 | 45620932 | 45620997 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |<br>
| 2884874 | 45587961 | 45588122 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |<br>
| 2884875 | 45567656 | 45567829 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |<br>
| 2884876 | 45513662 | 45513766 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |<br>
| 2884877 | 45507578 | 45507734 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |<br>
| 2884878 | 45498308 | 45498672 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |<br>
| 2884879 | 45404376 | 45404499 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |<br>
| 2884880 | 45404032 | 45404175 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |<br>
+-------------+----------+----------+-----------+-------------+-------------+--------+-----------------------+-------+------------+---------------+<br>
</blockquote>
<br>
When this query is runned inside my script using mysql, I get:<br>
<blockquote type="cite">bef<br>
SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id,
aligment.aligment_id, aligment.strand, aligment.reference_sequence_id,
aligment.score, aligment.distant_id, aligment.distant_table FROM
location me JOIN aligment aligment ON ( aligment.aligment_id =
me.aligment_id ) WHERE ( ( ( me.start*aligment.strand < ? ) AND (
me.aligment_id = ? ) ) ): '45622542', '507781'<br>
location id = 2884873<br>
location id = 2884874<br>
location id = 2884875<br>
location id = 2884876<br>
location id = 2884877<br>
location id = 2884878<br>
location id = 2884879<br>
location id = 2884880<br>
aft</blockquote>
<br>
When this query is runned inside my script using sqlite, I get:<br>
<blockquote type="cite">bef<br>
SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id,
aligment.aligment_id, aligment.strand, aligment.reference_sequence_id,
aligment.score, aligment.distant_id, aligment.distant_table FROM
location me JOIN aligment aligment ON ( aligment.aligment_id =
me.aligment_id ) WHERE ( ( ( me.start*aligment.strand < ? ) AND (
me.aligment_id = ? ) ) ): '45622542', '507781'<br>
location id = 2884872<br>
location id = 2884873<br>
location id = 2884874<br>
location id = 2884875<br>
location id = 2884876<br>
location id = 2884877<br>
location id = 2884878<br>
location id = 2884879<br>
location id = 2884880<br>
aft<br>
</blockquote>
Don't you find this weird. I run exactly the same query in sqlite
shell, and I dont get the same answer as inside DBIx::Class. When I run
this query using DBI only and sqlite, I get the right data fetched (the
same as mysql). As you can see, I removed the order by clause and I
still get the same response. The trace outputted above where done using
this code:<br>
<br>
<blockquote type="cite">sub prev_location { #only for stored
locations<br>
my ($self) = @_;<br>
warn("I am not sure this can work") unless ($self->in_storage);<br>
print STDERR "bef\n";<br>
my $x = $self->aligment->search_related_rs(<br>
'locations',<br>
{'me.start*aligment.strand' => {'<' => $self->start
* $self->aligment->strand}},<br>
# {order_by => ['(start*strand) DESC']}<br>
);<br>
my $first;<br>
<br>
while( my $b = $x->next){<br>
if(!defined($first)){<br>
$first=$b;<br>
}<br>
print STDERR "location id = " . $b->location_id . "\n";<br>
}<br>
print STDERR "aft\n";<br>
return $first;<br>
}</blockquote>
<br>
Here is the result source definition for alignment table:<br>
<blockquote type="cite">package Bio::Annotations::Aligment;<br>
use warnings;<br>
use strict;<br>
<br>
use base qw/DBIx::Class/;<br>
<br>
__PACKAGE__->load_components(qw/PK::Auto Core/);<br>
__PACKAGE__->table('aligment');<br>
__PACKAGE__->add_columns('aligment_id', 'strand',
'reference_sequence_id', 'score', 'distant_id',<br>
'distant_table');<br>
__PACKAGE__->set_primary_key('aligment_id');<br>
__PACKAGE__->belongs_to('reference_sequence',
'Bio::Annotations::ReferenceSequence',<br>
'reference_sequence_id');<br>
__PACKAGE__->belongs_to(<br>
'transcript',<br>
'Bio::Annotations::Transcript',<br>
{'foreign.transcript_id' => 'self.distant_id',},<br>
{ join => 'aligments',<br>
where => {'distant_table' => \" = 'transcript'"},<br>
}<br>
);<br>
<br>
__PACKAGE__->has_many(<br>
'locations',<br>
'Bio::Annotations::Location',<br>
'aligment_id',<br>
{ prefetch => ['aligment'],<br>
# order_by => ['start * strand', 'start']<br>
}<br>
);<br>
<br>
<br>
1;</blockquote>
and the location table:<br>
<blockquote type="cite">package Bio::Annotations::Location;<br>
use warnings;<br>
use strict;<br>
<br>
use base qw/DBIx::Class/;<br>
<br>
__PACKAGE__->load_components(qw/PK::Auto Core/);<br>
__PACKAGE__->table('location');<br>
__PACKAGE__->add_columns('location_id', 'start', 'end', 'accession',
'aligment_id');<br>
__PACKAGE__->set_primary_key('location_id');<br>
__PACKAGE__->belongs_to('aligment', 'Bio::Annotations::Aligment',
'aligment_id');<br>
<br>
sub prev_location { #only for stored locations<br>
my ($self) = @_;<br>
warn("I am not sure this can work") unless ($self->in_storage);<br>
print STDERR "bef\n";<br>
my $x = $self->aligment->search_related_rs(<br>
'locations',<br>
{'me.start*aligment.strand' => {'<' => $self->start
* $self->aligment->strand}},<br>
# {order_by => ['(start*strand) DESC']}<br>
);<br>
my $first;<br>
<br>
while( my $b = $x->next){<br>
if(!defined($first)){<br>
$first=$b;<br>
}<br>
print STDERR "location id = " . $b->location_id . "\n";<br>
}<br>
print STDERR "aft\n";<br>
return $first;<br>
}<br>
<br>
</blockquote>
<br>
Thanks again for your help...<br>
<br>
JF<br>
<br>
<br>
Zbigniew Lukasiak wrote:
<blockquote
cite="mid:d36a60910805152106o64b4edf1tba0fffd6cbe1d03c@mail.gmail.com"
type="cite">
<pre wrap="">Hello,
On Thu, May 15, 2008 at 10:15 PM, Jean-Francois Lucier
<a class="moz-txt-link-rfc2396E" href="mailto:Jean-Francois.Lucier@usherbrooke.ca"><Jean-Francois.Lucier@usherbrooke.ca></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hello,
I have build an API using DBIx::Class that executes many queries on either
SQLite or mysql (same database schema). I have put traces in my script.
When I execute on mysql, everything goes well and my traces output the
following:
trace1 selfstart=45622542 selfaligmentstrand=1
me.location_id : 2884872
SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id,
aligment.aligment_id, aligment.strand, aligment.reference_sequence_id,
aligment.score, aligment.distant_id, aligment.distant_table FROM location
me JOIN aligment aligment ON ( aligment.aligment_id = me.aligment_id )
WHERE ( ( ( start*strand < ? ) AND ( me.aligment_id = ? ) ) ) ORDER BY
start*strand DESC LIMIT 1: '45622542', '507781'
me.location_id : 2884873
for SQlite I get:
trace1 selfstart=45622542 selfaligmentstrand=1
me.location_id : 2884872
SELECT me.location_id, me.start, me.end, me.accession, me.aligment_id,
aligment.aligment_id, aligment.strand, aligment.reference_sequence_id,
aligment.score, aligment.distant_id, aligment.distant_table FROM location
me JOIN aligment aligment ON ( aligment.aligment_id = me.aligment_id )
WHERE ( ( ( start*strand < ? ) AND ( me.aligment_id = ? ) ) ) ORDER BY
start*strand DESC LIMIT 1: '45622542', '507781'
me.location_id : 2884872
If I use directly DBI and execute the same query, evrything goes ok and I
get:
the following query will be executed: SELECT me.location_id, me.start,
me.end, me.accession, me.aligment_id, aligment.aligment_id, aligment.strand,
aligment.reference_sequence_id, aligment.score, aligment.distant_id,
aligment.distant_table FROM location me JOIN aligment aligment ON (
aligment.aligment_id = me.aligment_id ) WHERE ( ( ( start*strand < 45622542
) AND ( me.aligment_id = 507781 ) ) ) ORDER BY start*strand DESC LIMIT 1
reference_sequence_id=27
score=1
location_id=2884873
distant_id=742163
aligment_id=507781
accession=
end=45620997
strand=1
distant_table=transcript
start=45620932
This is the code that executes the query inside DBIx::Class along with the
traces:
sub prev_location { #only for stored locations
my ($self) = @_;
warn("I am not sure this can work") unless ($self->in_storage);
print STDERR "trace1 selfstart=".$self->start."
selfaligmentstrand=".$self->aligment->strand."\n";
print STDERR "me.location_id : ".$self->location_id."\n";
my $x = $self->aligment->search_related_rs(
'locations',
{'start*strand' => {'<' => $self->start * $self->aligment->strand}},
{order_by => ['start*strand DESC']}
)->slice(0, 0)->single;
print STDERR "me.location_id : ".$x->location_id."\n";
return $x;
}
</pre>
</blockquote>
<pre wrap=""><!---->
Did you try to remove things from that query? For example remove:
'->slice(0, 0)->single' (the query will then return an array - but
that's OK - you just need to verify the SQL query after all),
'{order_by => ['start*strand DESC']}', maybe other parts as well. Try
to clean it a bit in general.
Then what would be also helpful is your ResultSource definitions.
</pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
IMPORTANT: Veuillez penser à l'environnement avant d'imprimer ce courriel
Jean-Francois Lucier
Bio-informaticien
B10 solutions, consultation en bioinformatique
Centre de recherche clinique du CHUS
3001, 12e avenue Nord
Sherbrooke (Québec) J1H 5N4
CANADA
Téléphone: (819) 820-6868 ext: 12577
Fax: (819) 564-5392</pre>
</body>
</html>