Re: Get the Chromosome Name For a Given Gene

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Re: Get the Chromosome Name For a Given Gene

Robert Buels
Hi Mark,

Please try to keep support requests for BCS on the gmod-schema mailing
list so that they are publicly searchable for future users.

Replies inline below.


Mark McDowall wrote:

> I am trying to get the Chromsome uniquename value given a gene's
> feature_id.   Due to the way that the data has been loaded into the
> database I get the feature_id for the chromosome that a gene is linked
> to via the featureloc table.   So using the srcfeature_id I want to get
> the uniquename from the feature table for the chromosome.
>
> However, if I use the command:
>
>    my $gene_chr = $chado->resultset('Sequence::Featureloc')
>              ->search({feature_id => $19418651})
>              ->search_related('srcfeature')
>
>
> I get the following error:
>
> DBIx::Class::ResultSet::next(): DBI Exception: DBD::Pg::st execute
> failed: ERROR:  column reference "feature_id" is ambiguous
> LINE 1: ... srcfeature.feature_id = me.srcfeature_id WHERE ( feature_id...
>                                                             ^ [for
> Statement "SELECT srcfeature.feature_id, srcfeature.dbxref_id,
> srcfeature.organism_id, srcfeature.name, srcfeature.uniquename,
> srcfeature.residues, srcfeature.seqlen, srcfeature.md5checksum,
> srcfeature.type_id, srcfeature.is_analysis, srcfeature.is_obsolete,
> srcfeature.timeaccessioned, srcfeature.timelastmodified FROM featureloc
> me JOIN feature srcfeature ON srcfeature.feature_id = me.srcfeature_id
> WHERE ( feature_id = ? )" with ParamValues: 1='19418651'] at
> geneBuilder.pl line 74
>
>
> The SQL does work, hever I have to convert the feature_id = ? to
> me.feature_id = ?.
>
> Is there a better way to formulate the query in the Perl code or a flag
> that I am missing that could provide a table reference for the feature_id?


No, this is the standard way that DBIx::Class source aliases work.  I
agree that it's a bit of a rough edge.  For more on this, and other
consequences of source aliases that you will no doubt run into as you
write more complex queries, see
http://search.cpan.org/~frew/DBIx-Class-0.08123/lib/DBIx/Class/ResultSet.pm#join

But on the bright side, once you learn it, it's still faster and easier
to write than the equivalent SQL.

Hope this helps!

Rob


------------------------------------------------------------------------------
Nokia and AT&T present the 2010 Calling All Innovators-North America contest
Create new apps & games for the Nokia N8 for consumers in  U.S. and Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
http://p.sf.net/sfu/nokia-dev2dev
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema