How to show the data for the chado_search?

[a2htray]

How can i fill the data to the select tag, and i had click the Populate button in the MViews configuration page?

is any guide to show the usage of chado_search?

[a2htray]

when i run drush trp-run-jobs --username=admin, the recent log message shows below

PDOException: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "featuremap_stock" does not exist LINE 39: ...|| species AS mapped_organism, O.organism_id FROM featuremap... ^: INSERT INTO {chado_search_marker_search} (SELECT DISTINCT MARKER.feature_id AS marker_feature_id, MARKER.uniquename AS marker_uniquename, SEQUENCEOF.feature_id AS seq_feature_id, SEQUENCEOF.seq_name AS seq_name, O.organism_id, O.genus || ' ' || O.species AS organism, FM.featuremap_id, FM.name AS map_name, STK.organism_id, STK.mapped_organism, LG.uniquename AS lg_uniquename, MTYPE.value AS marker_type, cast(START.value as real) AS start, cast(STOP.value as real) AS stop, LOC.srcfeature_id AS landmark_feature_id, LOC.uniquename AS landmark, LOC.fmin, LOC.fmax, LOC.name || ':' || (fmin + 1) || '..' || fmax AS location, ALIAS.value AS alias FROM feature MARKER INNER JOIN organism O ON O.organism_id = MARKER.organism_id LEFT JOIN (SELECT object_id, featuremap_id, map_feature_id, featurepos_id FROM feature LOCUS INNER JOIN feature_relationship FR ON FR.subject_id = LOCUS.feature_id INNER JOIN featurepos FS ON FS.feature_id = LOCUS.feature_id WHERE LOCUS.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'marker_locus' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence')) AND FR.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'instance_of' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'relationship')) ) MAP ON MAP.object_id = MARKER.feature_id LEFT JOIN (SELECT FR.object_id, V.name, seq.name AS seq_name, seq.feature_id, V2.name AS seq_type FROM feature seq INNER JOIN feature_relationship FR ON seq.feature_id = FR.subject_id INNER JOIN cvterm V ON cvterm_id = FR.type_id INNER JOIN cvterm V2 on V2.cvterm_id = seq.type_id WHERE FR.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'sequence_of')) SEQUENCEOF ON SEQUENCEOF.object_id = MARKER.feature_id LEFT JOIN featuremap FM ON FM.featuremap_id = MAP.featuremap_id LEFT JOIN (SELECT featuremap_id, genus || ' ' || species AS mapped_organism, O.organism_id FROM featuremap_stock FMS INNER JOIN stock S ON S.stock_id = FMS.stock_id INNER JOIN organism O ON S.organism_id = O.organism_id ) STK ON STK.featuremap_id = MAP.featuremap_id LEFT JOIN feature LG ON LG.feature_id = MAP.map_feature_id LEFT JOIN (SELECT feature_id, value FROM featureprop FP WHERE FP.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'marker_type' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'MAIN')) ) MTYPE ON MTYPE.feature_id = MARKER.feature_id LEFT JOIN (SELECT featurepos_id, value FROM featureposprop WHERE type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'start' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'MAIN')) ) START ON START.featurepos_id = MAP.featurepos_id LEFT JOIN (SELECT featurepos_id, value FROM featureposprop WHERE type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'stop' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'MAIN')) ) STOP ON STOP.featurepos_id = MAP.featurepos_id LEFT JOIN (SELECT max(MK.feature_id) AS feature_id, max(SRC.feature_id) AS srcfeature_id, max(SRC.name) AS name, max(SRC.uniquename) AS uniquename, max(SRCLOC.fmin) AS fmin, max(SRCLOC.fmax)AS fmax FROM feature MK INNER JOIN featureloc MATCHLOC ON MATCHLOC.srcfeature_id = MK.feature_id INNER JOIN featureloc SRCLOC ON MATCHLOC.feature_id = SRCLOC.feature_id INNER JOIN feature SRC ON SRC.feature_id = SRCLOC.srcfeature_id WHERE MK.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'genetic_marker' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence')) AND SRC.type_id IN (SELECT cvterm_id FROM cvterm WHERE name IN ('chromosome', 'supercontig') AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence')) GROUP BY (MK.feature_id, SRC.feature_id, SRC.name, SRCLOC.fmin, SRCLOC.fmax) ) LOC ON LOC.feature_id = MARKER.feature_id LEFT JOIN (SELECT feature_id, string_agg(value, '; ') AS value FROM featureprop FP WHERE FP.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'alias' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'MAIN')) GROUP BY feature_id ) ALIAS ON ALIAS.feature_id = MARKER.feature_id WHERE MARKER.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'genetic_marker' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence'))); Array ( ) in tripal_populate_mview() (line 440 of /var/www/html/sites/all/modules/tripal/tripal_core/api/tripal_core.mviews.api.inc).

[chunhuaicheng]

Hi,

Since Chado is a generic database schema, everyone stores data a bit differently. Therefore, you'll need to 1) load the data the same way as we do or 2) adjust the materialized view according to your data store.

Assuming you loaded your data using the Tripal Loader, the issue comes from that your genes are not linked to an analysis that has an analysisprop value of 'whole_genome' with the cvterm 'AnalysisType'.

Solution 1) Find the 'Analysis' that you used to load your sequences by going to the URL 'admin/content' and click on the 'edit' link next to it. Scroll down to the 'Properties' section and select 'AnalysisType' in the dropdown. Add a value 'whole_genome' and click 'Add' button. Save the form and repopulate the materialized view.

Solution 2) go to Administration > Tripal > Chado Schema > Materialized Views > Edit the SQL of 'chado_search_gene_search' materialized view in the 'Query' section: Remove the following part from the statement and save:

-- Restrict the sequences to 'unigenes' or 'reftrans WHERE (A.analysis_id IN ( SELECT analysis_id FROM analysisprop WHERE type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'Analysis Type') AND value IN ('unigene', 'whole_genome', 'bulk_data', 'ncbi_cotton_data')) )

Then, re-populate the materialized view. PS 1. If you ever want to get the default SQL back, simply delete the materialized view and use the command 'drush csreload' will recreate it.

PS 2. the ERROR about "featuremap_stock" is the similar, you'll need to adjust the 'chado_search_marker_search' materialized view accordingly or use our 'mainlab_tripal' module (https://github.com/tripal/mainlab_tripal) to create necessary custom tables.

[a2htray]

what does the "featuremap_stock" work? And i had read the guide, but it also can not work?

thanks for your help. since i'am not a biology researcher, i don't have any knowledge of the gene website.

And thank you again.

[chunhuaicheng]

Your chado schema does not have the 'featuremap_stock' table. If you install the 'mainlab_tripal' module, that table along with all other necessary custom tables will be created. Or you can run the following SQL to create it:

-- -- PostgreSQL database dump

SET search_path = chado, pg_catalog;

CREATE TABLE featuremap_stock ( featuremap_stock_id integer NOT NULL, featuremap_id integer NOT NULL, stock_id integer NOT NULL );

CREATE SEQUENCE featuremap_stock_featuremap_stock_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

ALTER SEQUENCE featuremap_stock_featuremap_stock_id_seq OWNED BY featuremap_stock.featuremap_stock_id;

ALTER TABLE ONLY featuremap_stock ALTER COLUMN featuremap_stock_id SET DEFAULT nextval('featuremap_stock_featuremap_stock_id_seq'::regclass);

ALTER TABLE ONLY featuremap_stock ADD CONSTRAINT featuremap_stock_c1 UNIQUE (featuremap_id, stock_id);

ALTER TABLE ONLY featuremap_stock ADD CONSTRAINT featuremap_stock_pkey PRIMARY KEY (featuremap_stock_id);

CREATE INDEX featuremap_stock_idx1 ON featuremap_stock USING btree (featuremap_id);

CREATE INDEX featuremap_stock_idx2 ON featuremap_stock USING btree (stock_id);

ALTER TABLE ONLY featuremap_stock ADD CONSTRAINT featuremap_stock_feature_id_fkey FOREIGN KEY (featuremap_id) REFERENCES featuremap(featuremap_id) ON DELETE CASCADE;

ALTER TABLE ONLY featuremap_stock ADD CONSTRAINT featuremap_stock_stock_id_fkey FOREIGN KEY (stock_id) REFERENCES stock(stock_id) ON DELETE CASCADE;

-- -- PostgreSQL database dump complete

[a2htray]

yeah, i had install the mainlab_tripal, and it shows no job when i run the command drush trp-run-jobs --username=admin after i click the populate button.

[chunhuaicheng]

Installing 'mainlab_tripal' module will only create the missing 'custom tables' (i.e. not materialized views). It will not populate the materialized views. You'll need to use the Tripal Admin interface (i.e. go to URL 'admin/tripal/schema/mviews') to submit Tripal Job(s) to populate the materialized view.

[a2htray]

yeah, it had populate the materialized view, but it shows Populated with 0 rows. And what operation can do it?

[chunhuaicheng]

I don't know which materialized view you were referring to. But you need to adjust it according to how the data are stored in your database. You can edit the materialized view by using the Tripal Admin interface. For example, if you're using the GDR gene search materialized view, you'll notice that it has an SQL statement more specific to the GDR environment. You'll need to remove those constraints from the SQL. On the other hand, if you're referring to the marker search materialized view, you can load the marker data using our mainlab chado loader: https://github.com/tripal/mainlab_chado_loader which will ensure you have the same data structure as we do.

[a2htray]

but i can not find the the section 'Upload Data'. whether the upload function is charging?

[chunhuaicheng]

I'm sorry but I do not understand your question. Is it a question for the 'mainlab_chado_loader'? If it is, can you post it on its project issues page so its developers can answer your question?

[a2htray]

yeah, follow your steps, i had completed the installation of chado_search, and selected the cottongen as the template. But i found that Typing the uri /mcl/ directly in the browser could jump to the page what i want. i wonder why these is no data to display. So i post an issue to the mainlab_chado_loader project, but no one answer, :(

[a2htray]

thank you, this issue could be closed.