[Gmod-tripal-devel] Adding a blast result filter to feature view

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[Gmod-tripal-devel] Adding a blast result filter to feature view

Michael Dondrup-3
Hi,
I am not sure if that is meant to work that way. After importing blast results with the Analysis:Blast importer,
I thought it could be a nice feature to add a Blast result filter to the Feature View. I added a Filter to the view for
Chado Blast Hit Data: Hit Description and Chado Blast Hit Data: Hit Name to filter for features having a blast hit
to a certain protein. I am not sure if that is supposed to work like this, the query for content ('shaven') I know is there yields
only a warning.

Thank you for your help

Michael



Here is the warning I get

        • warning: pg_query(): Query failed: ERROR: column reference "feature_id" is ambiguous at character 215 in /export/home/licebase/drupal/includes/database.pgsql.inc on line 138.
        • user warning: query: SELECT COUNT(*) FROM (SELECT node.nid AS node_nid FROM feature feature LEFT JOIN (SELECT array_agg(blast_hit_data.analysisfeature_id) as analysisfeature_id, array_agg(blast_hit_data.analysis_id) as analysis_id, feature_id, array_agg(blast_hit_data.db_id) as db_id, array_agg(blast_hit_data.hit_num) as hit_num, array_agg(blast_hit_data.hit_name) as hit_name, array_agg(blast_hit_data.hit_url) as hit_url, array_agg(blast_hit_data.hit_description) as hit_description, array_agg(blast_hit_data.hit_organism) as hit_organism, array_agg(blast_hit_data.blast_org_id) as blast_org_id, array_agg(blast_hit_data.hit_accession) as hit_accession, array_agg(blast_hit_data.hit_best_eval) as hit_best_eval, array_agg(blast_hit_data.hit_best_score) as hit_best_score, array_agg(blast_hit_data.hit_pid) as hit_pid, array_agg(fk_db_db_id.db_id) as fk_db_db_id_db_id, array_agg(fk_db_db_id.name) as fk_db_db_id_name, array_agg(fk_db_db_id.description) as fk_db_db_id_description, array_agg(fk_db_db_id.urlprefix) as fk_db_db_id_urlprefix, array_agg(fk_db_db_id.url) as fk_db_db_id_url, array_agg(fk_analysisfeature_analysisfeature_id.analysisfeature_id) as fk_analysisfeature_analysisfeature_id_analysisfeature_id, feature_id, array_agg(fk_analysisfeature_analysisfeature_id.analysis_id) as fk_analysisfeature_analysisfeature_id_analysis_id, array_agg(fk_analysisfeature_analysisfeature_id.rawscore) as fk_analysisfeature_analysisfeature_id_rawscore, array_agg(fk_analysisfeature_analysisfeature_id.normscore) as fk_analysisfeature_analysisfeature_id_normscore, array_agg(fk_analysisfeature_analysisfeature_id.significance) as fk_analysisfeature_analysisfeature_id_significance, array_agg(fk_analysisfeature_analysisfeature_id.identity) as fk_analysisfeature_analysisfeature_id_identity, array_agg(fk_analysis_analysis_id.analysis_id) as fk_analysis_analysis_id_analysis_id, array_agg(fk_analysis_analysis_id.name) as fk_analysis_analysis_id_name, array_agg(fk_analysis_analysis_id.description) as fk_analysis_analysis_id_description, array_agg(fk_analysis_analysis_id.program) as fk_analysis_analysis_id_program, array_agg(fk_analysis_analysis_id.programversion) as fk_analysis_analysis_id_programversion, array_agg(fk_analysis_analysis_id.algorithm) as fk_analysis_analysis_id_algorithm, array_agg(fk_analysis_analysis_id.sourcename) as fk_analysis_analysis_id_sourcename, array_agg(fk_analysis_analysis_id.sourceversion) as fk_analysis_analysis_id_sourceversion, array_agg(fk_analysis_analysis_id.sourceuri) as fk_analysis_analysis_id_sourceuri, array_agg(fk_analysis_analysis_id.timeexecuted) as fk_analysis_analysis_id_timeexecuted, array_agg(fk_blast_organisms_blast_org_id.blast_org_id) as fk_blast_organisms_blast_org_id_blast_org_id, array_agg(fk_blast_organisms_blast_org_id.blast_org_name) as fk_blast_organisms_blast_org_id_blast_org_name, array_agg(''||'analysisfeature_id::' || COALESCE(CAST(blast_hit_data.analysisfeature_id as text), '') || ',' || 'analysis_id::' || COALESCE(CAST(blast_hit_data.analysis_id as text), '') || ',' || 'feature_id::' || COALESCE(CAST(blast_hit_data.feature_id as text), '') || ',' || 'db_id::' || COALESCE(CAST(blast_hit_data.db_id as text), '') || ',' || 'hit_num::' || COALESCE(CAST(blast_hit_data.hit_num as text), '') || ',' || 'hit_name::' || COALESCE(CAST(blast_hit_data.hit_name as text), '') || ',' || 'hit_url::' || COALESCE(CAST(blast_hit_data.hit_url as text), '') || ',' || 'hit_description::' || COALESCE(CAST(blast_hit_data.hit_description as text), '') || ',' || 'hit_organism::' || COALESCE(CAST(blast_hit_data.hit_organism as text), '') || ',' || 'blast_org_id::' || COALESCE(CAST(blast_hit_data.blast_org_id as text), '') || ',' || 'hit_accession::' || COALESCE(CAST(blast_hit_data.hit_accession as text), '') || ',' || 'hit_best_eval::' || COALESCE(CAST(blast_hit_data.hit_best_eval as text), '') || ',' || 'hit_best_score::' || COALESCE(CAST(blast_hit_data.hit_best_score as text), '') || ',' || 'hit_pid::' || COALESCE(CAST(blast_hit_data.hit_pid as text), '') || ',' || 'fk_db_db_id_db_id::' || COALESCE(CAST(fk_db_db_id.db_id as text), '') || ',' || 'fk_db_db_id_name::' || COALESCE(CAST(fk_db_db_id.name as text), '') || ',' || 'fk_db_db_id_description::' || COALESCE(CAST(fk_db_db_id.description as text), '') || ',' || 'fk_db_db_id_urlprefix::' || COALESCE(CAST(fk_db_db_id.urlprefix as text), '') || ',' || 'fk_db_db_id_url::' || COALESCE(CAST(fk_db_db_id.url as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_analysisfeature_id::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.analysisfeature_id as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_feature_id::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.feature_id as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_analysis_id::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.analysis_id as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_rawscore::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.rawscore as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_normscore::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.normscore as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_significance::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.significance as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_identity::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.identity as text), '') || ',' || 'fk_analysis_analysis_id_analysis_id::' || COALESCE(CAST(fk_analysis_analysis_id.analysis_id as text), '') || ',' || 'fk_analysis_analysis_id_name::' || COALESCE(CAST(fk_analysis_analysis_id.name as text), '') || ',' || 'fk_analysis_analysis_id_description::' || COALESCE(CAST(fk_analysis_analysis_id.description as text), '') || ',' || 'fk_analysis_analysis_id_program::' || COALESCE(CAST(fk_analysis_analysis_id.program as text), '') || ',' || 'fk_analysis_analysis_id_programversion::' || COALESCE(CAST(fk_analysis_analysis_id.programversion as text), '') || ',' || 'fk_analysis_analysis_id_algorithm::' || COALESCE(CAST(fk_analysis_analysis_id.algorithm as text), '') || ',' || 'fk_analysis_analysis_id_sourcename::' || COALESCE(CAST(fk_analysis_analysis_id.sourcename as text), '') || ',' || 'fk_analysis_analysis_id_sourceversion::' || COALESCE(CAST(fk_analysis_analysis_id.sourceversion as text), '') || ',' || 'fk_analysis_analysis_id_sourceuri::' || COALESCE(CAST(fk_analysis_analysis_id.sourceuri as text), '') || ',' || 'fk_analysis_analysis_id_timeexecuted::' || COALESCE(CAST(fk_analysis_analysis_id.timeexecuted as text), '') || ',' || 'fk_blast_organisms_blast_org_id_blast_org_id::' || COALESCE(CAST(fk_blast_organisms_blast_org_id.blast_org_id as text), '') || ',' || 'fk_blast_organisms_blast_org_id_blast_org_name::' || COALESCE(CAST(fk_blast_organisms_blast_org_id.blast_org_name as text), '')||'') as all FROM blast_hit_data LEFT JOIN db fk_db_db_id ON blast_hit_data.db_id=fk_db_db_id.db_id LEFT JOIN analysisfeature fk_analysisfeature_analysisfeature_id ON blast_hit_data.analysisfeature_id=fk_analysisfeature_analysisfeature_id.analysisfeature_id LEFT JOIN analysis fk_analysis_analysis_id ON blast_hit_data.analysis_id=fk_analysis_analysis_id.analysis_id LEFT JOIN blast_organisms fk_blast_organisms_blast_org_id ON blast_hit_data.blast_org_id=fk_blast_organisms_blast_org_id.blast_org_id WHERE blast_hit_data.hit_description ~ 'shaven' AND blast_hit_data.hit_description ~ 'shaven' GROUP BY feature_id) blast_hit_data ON feature.feature_id = blast_hit_data.feature_id LEFT JOIN chado_feature chado_feature ON feature.feature_id = chado_feature.feature_id LEFT JOIN node node ON chado_feature.nid = node.nid LEFT JOIN cvterm cvterm ON feature.type_id = cvterm.cvterm_id LEFT JOIN organism organism ON feature.organism_id = organism.organism_id WHERE (feature.type_id IN (283)) AND (UPPER(feature.name) LIKE UPPER('%ago%')) AND (blast_hit_data.hit_description IS NOT NULL) ) count_alias in /export/home/licebase/drupal/sites/all/modules/views/includes/view.inc on line 805.
------------------------------------------------------------------------------
Introducing Performance Central, a new site from SourceForge and
AppDynamics. Performance Central is your source for news, insights,
analysis and resources for efficient Application Performance Management.
Visit us today!
http://pubads.g.doubleclick.net/gampad/clk?id=48897511&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel
Reply | Threaded
Open this post in threaded view
|

Re: [Gmod-tripal-devel] Adding a blast result filter to feature view

Michael Dondrup-3
Please forget about this, I got it.


On Aug 25, 2013, at 12:12 PM, Michael Dondrup wrote:

> Hi,
> I am not sure if that is meant to work that way. After importing blast results with the Analysis:Blast importer,
> I thought it could be a nice feature to add a Blast result filter to the Feature View. I added a Filter to the view for
> Chado Blast Hit Data: Hit Description and Chado Blast Hit Data: Hit Name to filter for features having a blast hit
> to a certain protein. I am not sure if that is supposed to work like this, the query for content ('shaven') I know is there yields
> only a warning.
>
> Thank you for your help
>
> Michael
>
>
>
> Here is the warning I get
>
> • warning: pg_query(): Query failed: ERROR: column reference "feature_id" is ambiguous at character 215 in /export/home/licebase/drupal/includes/database.pgsql.inc on line 138.
> • user warning: query: SELECT COUNT(*) FROM (SELECT node.nid AS node_nid FROM feature feature LEFT JOIN (SELECT array_agg(blast_hit_data.analysisfeature_id) as analysisfeature_id, array_agg(blast_hit_data.analysis_id) as analysis_id, feature_id, array_agg(blast_hit_data.db_id) as db_id, array_agg(blast_hit_data.hit_num) as hit_num, array_agg(blast_hit_data.hit_name) as hit_name, array_agg(blast_hit_data.hit_url) as hit_url, array_agg(blast_hit_data.hit_description) as hit_description, array_agg(blast_hit_data.hit_organism) as hit_organism, array_agg(blast_hit_data.blast_org_id) as blast_org_id, array_agg(blast_hit_data.hit_accession) as hit_accession, array_agg(blast_hit_data.hit_best_eval) as hit_best_eval, array_agg(blast_hit_data.hit_best_score) as hit_best_score, array_agg(blast_hit_data.hit_pid) as hit_pid, array_agg(fk_db_db_id.db_id) as fk_db_db_id_db_id, array_agg(fk_db_db_id.name) as fk_db_db_id_name, array_agg(fk_db_db_id.description) as fk_db_db_id_description, array_agg(fk_db_db_id.urlprefix) as fk_db_db_id_urlprefix, array_agg(fk_db_db_id.url) as fk_db_db_id_url, array_agg(fk_analysisfeature_analysisfeature_id.analysisfeature_id) as fk_analysisfeature_analysisfeature_id_analysisfeature_id, feature_id, array_agg(fk_analysisfeature_analysisfeature_id.analysis_id) as fk_analysisfeature_analysisfeature_id_analysis_id, array_agg(fk_analysisfeature_analysisfeature_id.rawscore) as fk_analysisfeature_analysisfeature_id_rawscore, array_agg(fk_analysisfeature_analysisfeature_id.normscore) as fk_analysisfeature_analysisfeature_id_normscore, array_agg(fk_analysisfeature_analysisfeature_id.significance) as fk_analysisfeature_analysisfeature_id_significance, array_agg(fk_analysisfeature_analysisfeature_id.identity) as fk_analysisfeature_analysisfeature_id_identity, array_agg(fk_analysis_analysis_id.analysis_id) as fk_analysis_analysis_id_analysis_id, array_agg(fk_analysis_analysis_id.name) as fk_analysis_analysis_id_name, array_agg(fk_analysis_analysis_id.description) as fk_analysis_analysis_id_description, array_agg(fk_analysis_analysis_id.program) as fk_analysis_analysis_id_program, array_agg(fk_analysis_analysis_id.programversion) as fk_analysis_analysis_id_programversion, array_agg(fk_analysis_analysis_id.algorithm) as fk_analysis_analysis_id_algorithm, array_agg(fk_analysis_analysis_id.sourcename) as fk_analysis_analysis_id_sourcename, array_agg(fk_analysis_analysis_id.sourceversion) as fk_analysis_analysis_id_sourceversion, array_agg(fk_analysis_analysis_id.sourceuri) as fk_analysis_analysis_id_sourceuri, array_agg(fk_analysis_analysis_id.timeexecuted) as fk_analysis_analysis_id_timeexecuted, array_agg(fk_blast_organisms_blast_org_id.blast_org_id) as fk_blast_organisms_blast_org_id_blast_org_id, array_agg(fk_blast_organisms_blast_org_id.blast_org_name) as fk_blast_organisms_blast_org_id_blast_org_name, array_agg(''||'analysisfeature_id::' || COALESCE(CAST(blast_hit_data.analysisfeature_id as text), '') || ',' || 'analysis_id::' || COALESCE(CAST(blast_hit_data.analysis_id as text), '') || ',' || 'feature_id::' || COALESCE(CAST(blast_hit_data.feature_id as text), '') || ',' || 'db_id::' || COALESCE(CAST(blast_hit_data.db_id as text), '') || ',' || 'hit_num::' || COALESCE(CAST(blast_hit_data.hit_num as text), '') || ',' || 'hit_name::' || COALESCE(CAST(blast_hit_data.hit_name as text), '') || ',' || 'hit_url::' || COALESCE(CAST(blast_hit_data.hit_url as text), '') || ',' || 'hit_description::' || COALESCE(CAST(blast_hit_data.hit_description as text), '') || ',' || 'hit_organism::' || COALESCE(CAST(blast_hit_data.hit_organism as text), '') || ',' || 'blast_org_id::' || COALESCE(CAST(blast_hit_data.blast_org_id as text), '') || ',' || 'hit_accession::' || COALESCE(CAST(blast_hit_data.hit_accession as text), '') || ',' || 'hit_best_eval::' || COALESCE(CAST(blast_hit_data.hit_best_eval as text), '') || ',' || 'hit_best_score::' || COALESCE(CAST(blast_hit_data.hit_best_score as text), '') || ',' || 'hit_pid::' || COALESCE(CAST(blast_hit_data.hit_pid as text), '') || ',' || 'fk_db_db_id_db_id::' || COALESCE(CAST(fk_db_db_id.db_id as text), '') || ',' || 'fk_db_db_id_name::' || COALESCE(CAST(fk_db_db_id.name as text), '') || ',' || 'fk_db_db_id_description::' || COALESCE(CAST(fk_db_db_id.description as text), '') || ',' || 'fk_db_db_id_urlprefix::' || COALESCE(CAST(fk_db_db_id.urlprefix as text), '') || ',' || 'fk_db_db_id_url::' || COALESCE(CAST(fk_db_db_id.url as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_analysisfeature_id::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.analysisfeature_id as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_feature_id::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.feature_id as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_analysis_id::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.analysis_id as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_rawscore::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.rawscore as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_normscore::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.normscore as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_significance::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.significance as text), '') || ',' || 'fk_analysisfeature_analysisfeature_id_identity::' || COALESCE(CAST(fk_analysisfeature_analysisfeature_id.identity as text), '') || ',' || 'fk_analysis_analysis_id_analysis_id::' || COALESCE(CAST(fk_analysis_analysis_id.analysis_id as text), '') || ',' || 'fk_analysis_analysis_id_name::' || COALESCE(CAST(fk_analysis_analysis_id.name as text), '') || ',' || 'fk_analysis_analysis_id_description::' || COALESCE(CAST(fk_analysis_analysis_id.description as text), '') || ',' || 'fk_analysis_analysis_id_program::' || COALESCE(CAST(fk_analysis_analysis_id.program as text), '') || ',' || 'fk_analysis_analysis_id_programversion::' || COALESCE(CAST(fk_analysis_analysis_id.programversion as text), '') || ',' || 'fk_analysis_analysis_id_algorithm::' || COALESCE(CAST(fk_analysis_analysis_id.algorithm as text), '') || ',' || 'fk_analysis_analysis_id_sourcename::' || COALESCE(CAST(fk_analysis_analysis_id.sourcename as text), '') || ',' || 'fk_analysis_analysis_id_sourceversion::' || COALESCE(CAST(fk_analysis_analysis_id.sourceversion as text), '') || ',' || 'fk_analysis_analysis_id_sourceuri::' || COALESCE(CAST(fk_analysis_analysis_id.sourceuri as text), '') || ',' || 'fk_analysis_analysis_id_timeexecuted::' || COALESCE(CAST(fk_analysis_analysis_id.timeexecuted as text), '') || ',' || 'fk_blast_organisms_blast_org_id_blast_org_id::' || COALESCE(CAST(fk_blast_organisms_blast_org_id.blast_org_id as text), '') || ',' || 'fk_blast_organisms_blast_org_id_blast_org_name::' || COALESCE(CAST(fk_blast_organisms_blast_org_id.blast_org_name as text), '')||'') as all FROM blast_hit_data LEFT JOIN db fk_db_db_id ON blast_hit_data.db_id=fk_db_db_id.db_id LEFT JOIN analysisfeature fk_analysisfeature_analysisfeature_id ON blast_hit_data.analysisfeature_id=fk_analysisfeature_analysisfeature_id.analysisfeature_id LEFT JOIN analysis fk_analysis_analysis_id ON blast_hit_data.analysis_id=fk_analysis_analysis_id.analysis_id LEFT JOIN blast_organisms fk_blast_organisms_blast_org_id ON blast_hit_data.blast_org_id=fk_blast_organisms_blast_org_id.blast_org_id WHERE blast_hit_data.hit_description ~ 'shaven' AND blast_hit_data.hit_description ~ 'shaven' GROUP BY feature_id) blast_hit_data ON feature.feature_id = blast_hit_data.feature_id LEFT JOIN chado_feature chado_feature ON feature.feature_id = chado_feature.feature_id LEFT JOIN node node ON chado_feature.nid = node.nid LEFT JOIN cvterm cvterm ON feature.type_id = cvterm.cvterm_id LEFT JOIN organism organism ON feature.organism_id = organism.organism_id WHERE (feature.type_id IN (283)) AND (UPPER(feature.name) LIKE UPPER('%ago%')) AND (blast_hit_data.hit_description IS NOT NULL) ) count_alias in /export/home/licebase/drupal/sites/all/modules/views/includes/view.inc on line 805.
> ------------------------------------------------------------------------------
> Introducing Performance Central, a new site from SourceForge and
> AppDynamics. Performance Central is your source for news, insights,
> analysis and resources for efficient Application Performance Management.
> Visit us today!
> http://pubads.g.doubleclick.net/gampad/clk?id=48897511&iu=/4140/ostg.clktrk
> _______________________________________________
> Gmod-tripal-devel mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel


------------------------------------------------------------------------------
Introducing Performance Central, a new site from SourceForge and
AppDynamics. Performance Central is your source for news, insights,
analysis and resources for efficient Application Performance Management.
Visit us today!
http://pubads.g.doubleclick.net/gampad/clk?id=48897511&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel