Feature Search w/ Synonyms MView

The materialized view chado_feature_synonyms_list</span> described in this page is part of the tutorial on how to Expand Feature Search to Include Synonyms (http://tripal.info/node/136/). The materialized view focuses on feature synonyms, but the code can however be easily adapted to support other types of synonymes (cvterm synonyms, library synonyms and so forth). The included sql code uses the Postgres functions, array_agg, supported from Postgres version 8.4 If you are using an older version the following code would not work. Also, that would mean that you are using a really old database installation, please consider upgrading.
Materialized View Name: 
Feature Search w/ Synonyms
Materialized View Computer-Readable Name: 
chado_feature_synonyms_list
Authors: 
Nelson Nazzicari
Compatible Chado version: 
Compatible Tripal Version: 
Table Schema: 
array ( 'description' => 'this view lists, for each feature, its id, its uniquename, all the recorded synonyms (with and without its uniquename)', 'table' => 'chado_feature_synonyms_list', 'fields' => array ( 'feature_id' => array ( 'type' => 'int', 'not null' => true, ), 'uniquename' => array ( 'type' => 'text', 'not null' => true, ), 'name' => array ( 'type' => 'text', 'not null' => false, ), 'synonyms_list' => array ( 'type' => 'text', 'not null' => true, ), 'uniquename_and_synonyms_list' => array ( 'type' => 'text', 'not null' => true, ), ), 'indexes' => array ( 'feature_id_idx' => array ( 0 => 'feature_id', ), 'uniquename_idx' => array ( 0 => 'uniquename', ), ), )
SQL Query: 
SELECT feature.feature_id, feature.uniquename, feature.name, array_to_string(array_agg(synonym.name), ' '), feature.uniquename || ' ' || array_to_string(array_agg(synonym.name), ' ') || ' ' || feature.name FROM feature LEFT OUTER JOIN feature_synonym ON (feature.feature_id = feature_synonym.feature_id) LEFT OUTER JOIN synonym ON (synonym.synonym_id = feature_synonym.synonym_id) GROUP BY feature.feature_id, feature.uniquename, feature.name
Detailed Description: 

The materialized chado_feature_synonyms_list contains as many rows as saved features in the Chado database. It has five fields:

  1. feature_id: this field reports the feature id, and it will be used to join this table to feature table
  2. uniquename: it's the feature uniquename field
  3. name: it's the feature name field
  4. synonyms_list: it's a list of the feature synonymes, separated by spaces
  5. uniquename_and_synonyms_list: it's the concatenation of the previous three fields. Useful for search operations.

To import this materialized view, simply paste the following definitions into the materialized views of your site (usually under <your_site>/admin/tripal/schema/mviews) and hit "Add".

The tutorial explains in more details the required operations.