[pygr-notify] Issue 53 in pygr: sqlgraph.GraphView ORDER-BY bug

[codesite-noreply at google.com]

Status: Accepted
Owner: jqian.ubc
CC: cjlee112
Labels: Type-Defect Priority-High

New issue 53 by jqian.ubc: sqlgraph.GraphView ORDER-BY bug
http://code.google.com/p/pygr/issues/detail?id=53

What steps will reproduce the problem?

qing at 1[ensembl]$ python -i
Python 2.5.2 (r252:60911, Aug  8 2008, 09:22:44)
[GCC 4.3.1] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pygr.Data
>>> from ensembl import adaptor
>>> from pygr import sqlgraph
>>> conn = pygr.Data.Bio.Server.Ensembl.Ensembldb()
>>> translationTB =
sqlgraph.SQLTable('homo_sapiens_core_47_36i.translation',
itemClass=sqlgraph.TupleO, serverInfo=conn)
>>> exonTB = sqlgraph.SQLTable('homo_sapiens_core_47_36i.exon',
itemClass=sqlgraph.TupleO, serverInfo=conn)
>>> cursor = translationTB.cursor
>>> sql_statement = 'SELECT t3.exon_id FROM
homo_sapiens_core_47_36i.translation AS tr,
homo_sapiens_core_47_36i.exon_transcript AS t1,
homo_sapiens_core_47_36i.exon_transcript AS t2,
homo_sapiens_core_47_36i.exon_transcript AS t3 WHERE tr.translation_id = %s
AND tr.transcript_id = t1.transcript_id AND t1.transcript_id =
t2.transcript_id AND t2.transcript_id = t3.transcript_id AND t1.exon_id =
tr.start_exon_id AND t2.exon_id = tr.end_exon_id AND t3.rank >= t1.rank AND
t3.rank <= t2.rank ORDER BY t3.rank'
>>> translationExons = sqlgraph.GraphView(translationTB, exonTB,
sql_statement, cursor)
>>> translation = translationTB[15121]
>>> exons = translationExons[translation]
>>> for e in exons:
...     print e.id
...
95069
95088
95101
95020
95050
95035
95081
95110
95059
95160
95172



What is the expected output?

mysql> SELECT t3.exon_id FROM homo_sapiens_core_47_36i.translation AS tr,
homo_sapiens_core_47_36i.exon_transcript AS t1,
homo_sapiens_core_47_36i.exon_transcript AS t2,
homo_sapiens_core_47_36i.exon_transcript AS t3 WHERE tr.translation_id =
15121 AND tr.transcript_id = t1.transcript_id AND t1.transcript_id =
t2.transcript_id AND t2.transcript_id = t3.transcript_id AND t1.exon_id =
tr.start_exon_id AND t2.exon_id = tr.end_exon_id AND t3.rank >= t1.rank AND
t3.rank <= t2.rank ORDER BY t3.rank;

+---------+
| exon_id |
+---------+
|   95160 |
|   95020 |
|   95035 |
|   95050 |
|   95059 |
|   95069 |
|   95081 |
|   95088 |
|   95101 |
|   95110 |
|   95172 |
+---------+


Please use labels and text to provide additional information.

Since 'ORDER BY' is such a common operation in a sql muti-table join
statement, it would be really cool if sqlgraph.GraphView could support it.

--
You received this message because you are listed in the owner
or CC fields of this issue, or because you starred this issue.
You may adjust your issue notification preferences at:
http://code.google.com/hosting/settings