tripal / chado_search

Mainlab Chado Search: enabling advanced search function for a Tripal/Chado database

Tripal 4 (Drupal 10)

               _   _                 _____                 _                                  _
     /\       | | (_)               |  __ \               | |                                | |
    /  \   ___| |_ ___   _____      | |  | | _____   _____| | ___  _ __  _ __ ___   ___ _ __ | |_
   / /\ \ / __| __| \ \ / / _ \     | |  | |/ _ \ \ / / _ \ |/ _ \| '_ \| '_ ` _ \ / _ \ '_ \| __|
  / ____ \ (__| |_| |\ V /  __/     | |__| |  __/\ V /  __/ | (_) | |_) | | | | | |  __/ | | | |_
 /_/    \_\___|\__|_| \_/ \___|     |_____/ \___| \_/ \___|_|\___/| .__/|_| |_| |_|\___|_| |_|\__|
                                                                  | |
                                                                  |_|

Compatibility

Tested on +

Drupal	10.0.x	10.1.x	10.2.x
PHP 8.1
PHP 8.2
PHP 8.3

Code Coverage

This project uses Code Climate to determine the quality of our codebase and the coverage of our test suite. Compatibility above is based on passing of this test suite.

Current Timeline

Required for Tripal 4.alpha3 Release

• Display suite / page work
• Importers: Publications
• Search (ElasticSearch + Views)

How to get involved!

This upgrade to Drupal 10 is a community effort. As such, we NEED YOUR HELP!

• To get involved, please join our Tripal Slack and comment in the #core-dev channel.
  • Alternatively, feel free to contact Lacey-Anne Sanderson through Slack direct message.
  • We can use help both with programming, documentation, outreach and welcome all individuals from all backgrounds!
• We prefer automated testing for all Pull Requests (PRs) and are happy to guide you through the process!
  • Just comment on the issue if you need help designing tests
  • If you need help implementing tests see our documentation
• If more documentation would be helpful, please create an issue here and we'll add it.

Documentation

Documentation for Tripal 4 has begun on ReadtheDocs.

• Installation
• Automated Testing
• Developer Guide
• Contribution Guide

Tripal Docker

Tripal Docker is currently focused on Development and Automated Testing. There will be a production focused Tripal Docker soon and if you're interested in helping or providing tips -please join us on our Slack channel!

For more information about how to use our fully functional development docker, see our documentation on ReadtheDocs!

chado_search's Issues

How to show the data for the chado_search?

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?

:( there is one column not in table

the column "landmark" not in the chado_search_gene_search table

throw the phpexception, any ways to solve it?

PDOException: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "chado.chado_search_sequence_search" does not exist LINE 1: SELECT distinct organism FROM chado.chado_search_sequence_se... ^: SELECT distinct organism FROM chado.chado_search_sequence_search ORDER BY organism; Array ( ) in chado_query() (line 1538 of /var/www/html/sites/all/modules/tripal/tripal_core/api/tripal_core.chado_query.api.inc).

Error messages about cottongen.settings.txt

Dear all, I installed chado_search by using process below.

git clone https://gitlab.com/mainlabwsu/chado_search.git cp cottongen.settings.txt settings.conf drush pm-enable chado_search

However, when I entered search page (http://localhost/find/qualitative_traits), error message was shown below.

Unable to create SelectFilter form element. Please check your settings. SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "chado_search_qualitative_traits" does not exist LINE 1: ...lic, chado; SELECT distinct trait_descriptor FROM chado_sear... ^ Unable to bind DynamicSelectFilter form element. Please check your SQL statement in the AJAX callback. SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "chado_search_qualitative_traits" does not exist LINE 1: ..., public, chado; SELECT distinct trait_value FROM chado_sear... ^ Unable to create SelectFilter form element. Please check your settings. SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "chado_search_qualitative_traits" does not exist LINE 1: ...lic, chado; SELECT distinct trait_descriptor FROM chado_sear... ^ Unable to bind DynamicSelectFilter form element. Please check your SQL statement in the AJAX callback. SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "chado_search_qualitative_traits" does not exist LINE 1: ..., public, chado; SELECT distinct trait_value FROM chado_sear... ^ Unable to create SelectFilter form element. Please check your settings. SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "chado_search_qualitative_traits" does not exist LINE 1: ...lic, chado; SELECT distinct trait_descriptor FROM chado_sear... ^ Unable to bind DynamicSelectFilter form element. Please check your SQL statement in the AJAX callback. SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "chado_search_qualitative_traits" does not exist LINE 1: ..., public, chado; SELECT distinct trait_value FROM chado_sear... ^

Is there any to solve this error?

Best,

relation "feature" does not exist

hi, @chunhuaicheng

i need to populate the chado_search_gene_search MV when i use chado_search, but i show 0 row after executing the drush trp-run-jobs --username=admin --root=/var/www/html command. So i execute the MV query below

SELECT
F.feature_id,
F.name AS feature_name,
F.uniquename AS feature_uniquename,
F.seqlen AS feature_seqlen,
(SELECT genus || ' ' || species FROM organism WHERE organism_id = F.organism_id) AS organism,
(SELECT genus FROM organism WHERE organism_id = F.organism_id),
(SELECT species FROM organism WHERE organism_id = F.organism_id),
(SELECT common_name FROM organism WHERE organism_id = F.organism_id) AS organism_common_name,
(SELECT name FROM cvterm WHERE cvterm_id = F.type_id) AS feature_type,
LOC.srcfeature_id,
LOC.name AS landmark,
(fmin + 1) AS fmin,
fmax,
LOC.name || ':' || (fmin + 1) || '..' || fmax AS location,
A.name AS analysis,
-- Blast Best Hit
(SELECT string_agg(distinct
(SELECT array_to_string(regexp_matches(value, '<Hit_def>(.+?)</Hit_def>'), '')
FROM analysisfeatureprop AFP2 WHERE AFP2.analysisfeatureprop_id = AFP.analysisfeatureprop_id)
, '. ')
FROM analysisfeatureprop AFP
INNER JOIN analysisfeature AF2 ON AF2.analysisfeature_id = AFP.analysisfeature_id
WHERE
type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'analysis_blast_output_iteration_hits')
AND
AF2.feature_id = F.feature_id
) AS blast_value,
-- KEGG
(SELECT string_agg(distinct
(SELECT trim(regexp_replace(value, '<.+>', ''))
FROM analysisfeatureprop AFP2 WHERE AFP2.analysisfeatureprop_id = AFP.analysisfeatureprop_id)
, '. ')
FROM analysisfeatureprop AFP
INNER JOIN analysisfeature AF2 ON AF2.analysisfeature_id = AFP.analysisfeature_id
WHERE
type_id IN (SELECT cvterm_id FROM cvterm WHERE cv_id = (SELECT cv_id FROM cv WHERE name = 'KEGG_ORTHOLOGY' or name = 'KEGG_PATHWAYS'))
AND
AF2.feature_id = F.feature_id
) AS kegg_value,
-- Interpro
(
SELECT string_agg(distinct value, '. ')
FROM (
SELECT
AF2.feature_id,
array_to_string (regexp_matches(value, 'name="(.+?)"', 'g'), '') AS value
FROM analysisfeatureprop AFP2
INNER JOIN analysisfeature AF2 ON AF2.analysisfeature_id = AFP2.analysisfeature_id
WHERE AFP2.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'analysis_interpro_xmloutput_hit')
AND AF2.feature_id = F.feature_id
) INTERPRO GROUP BY feature_id
) AS interpro_value,
-- GO term
(
SELECT string_agg(distinct name, '. ')
FROM (
SELECT feature_id,
V.name
FROM feature_cvterm FC
INNER JOIN cvterm V ON V.cvterm_id = FC.cvterm_id
WHERE FC.feature_id = F.feature_id
AND cv_id IN (SELECT cv_id FROM cv WHERE name IN ('biological_process', 'cellular_component', 'molecular_function'))
) GOTERM GROUP BY feature_id
) AS go_term,
-- Genbank Keywords
(
SELECT string_agg(value, '. ')
FROM featureprop
WHERE type_id IN
(SELECT cvterm_id
FROM cvterm
WHERE cv_id =
(SELECT cv_id
FROM cv
WHERE name = 'tripal_genbank_parser')
AND name IN ('genbank_note','genbank_gene','product','function','genbank_detail','genbank_description'))
AND feature_id = F.feature_id
GROUP BY feature_id
) AS gb_keyword
-- Base Table
FROM feature F
INNER JOIN analysisfeature AF ON AF.feature_id = F.feature_id
INNER JOIN analysis A ON A.analysis_id = AF.analysis_id
-- Genome Location
LEFT JOIN
((SELECT
GENE.feature_id,
LMARK.srcfeature_id,
LMARK.fmin,
LMARK.fmax,
LMARK.name
FROM Feature GENE
INNER JOIN featureloc GMATLOC ON GMATLOC.srcfeature_id = GENE.feature_id
INNER JOIN (
SELECT
LMATLOC.feature_id,
LMATLOC.srcfeature_id,
LMATLOC.fmin,
LMATLOC.fmax,
CHR.name
FROM Feature CHR
INNER JOIN featureloc LMATLOC ON LMATLOC.srcfeature_id = CHR.feature_id
WHERE (SELECT type_id FROM feature F WHERE F.feature_id = LMATLOC.feature_id) = (SELECT cvterm_id FROM cvterm WHERE name = 'match' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence'))
AND CHR.type_id IN (SELECT cvterm_id FROM cvterm WHERE name IN ('chromosome', 'supercontig') AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence'))
) LMARK ON LMARK.feature_id = GMATLOC.feature_id
WHERE GENE.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'gene' AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence')))
UNION
(SELECT FL.feature_id, srcfeature_id, fmin, fmax, F.name FROM featureloc FL
INNER JOIN feature F ON F.feature_id = FL.srcfeature_id
WHERE F.type_id IN (SELECT cvterm_id FROM cvterm WHERE name IN ('chromosome', 'supercontig') AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence'))
)
) LOC ON LOC.feature_id = AF.feature_id
WHERE
(
-- Restrict the sequence type to gene/mRNA/contig for Unigene/RefTrans
(F.type_id IN (SELECT cvterm_id FROM cvterm WHERE name IN ('contig', 'gene', 'mRNA') AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence'))) AND
(A.analysis_id IN (SELECT analysis_id FROM analysisprop WHERE type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'Analysis Type') AND value IN ('reftrans', 'unigene', 'bulk_data', 'ncbi_data', 'transcriptome', 'other_transcripts')))
OR
-- Restrict the sequence type to gene/mRNA for whole genome assembly
(F.type_id IN (SELECT cvterm_id FROM cvterm WHERE name IN ('gene', 'mRNA') AND cv_id = (SELECT cv_id FROM cv WHERE name = 'sequence'))) AND
(A.analysis_id IN (SELECT analysis_id FROM analysisprop WHERE type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'Analysis Type') AND value = 'whole_genome'))
)

and throw an exception [42P01] ERROR: relation "feature" does not exist, i don't kown where the feature get from.