germinate_template_4_0_0
Database
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
export_trials_data
Parameters
Name
Type
Mode
groupIds
text
IN
markedIds
text
IN
datasetIds
text
IN
phenotypeIds
text
IN
Definition
BEGIN SET @SQL = NULL; SET @@group_concat_max_len = 64000000; SET @QRY = CONCAT(' SELECT GROUP_CONCAT(DISTINCT CONCAT("MAX(IF(`phenotype_id` = ", `phenotype_id`,",phenotype_value,NULL)) AS ", "`", CONCAT(phenotypes.`name`, IF(ISNULL(phenotypes.unit_id), "", CONCAT(" [", units.unit_abbreviation, "]"))), "`")) INTO @SQL FROM phenotypedata LEFT JOIN phenotypes ON phenotypes.id = phenotypedata.phenotype_id LEFT JOIN germinatebase ON germinatebase.id = phenotypedata.germinatebase_id LEFT JOIN units ON units.id = phenotypes.unit_id WHERE phenotypedata.dataset_id IN (', datasetIds, ') AND ', IF(phenotypeIds IS NULL, '1=1', CONCAT('phenotype_id IN (', phenotypeIds, ')'))); PREPARE stmtone FROM @QRY; EXECUTE stmtone; DEALLOCATE PREPARE stmtone; IF @SQL IS NULL THEN SELECT NULL as ERROR; ELSE SET @SQL = CONCAT(' SELECT germinatebase.name, germinatebase.puid, `g`.`name` AS `entity_parent_name`, `g`.`general_identifier` AS `entity_parent_general_identifier`, datasets.name AS dataset_name, datasets.description AS dataset_description, datasets.version AS dataset_version, licenses.name AS license_name, ( SELECT GROUP_CONCAT(site_name SEPARATOR ", ") FROM locations LEFT JOIN datasetlocations ON datasetlocations.location_id = locations.id WHERE datasetlocations.dataset_id = phenotypedata.dataset_id ) AS location_name, ( SELECT site_name FROM locations WHERE locations.id = phenotypedata.location_id ) AS trial_site, DATE_FORMAT(phenotypedata.recording_date, '%Y') AS year, DATE_FORMAT(phenotypedata.recording_date, '%Y-%m-%d') AS Date, germinatebase.id AS dbId, germinatebase.general_identifier, treatments.description AS treatments_description, ', IF(groupIds IS NULL, '', CONCAT('(SELECT json_arrayagg(CONCAT(LEFT(groups.name, 10), IF(LENGTH(groups.name)>10, "...", ""))) FROM groupmembers LEFT JOIN groups ON groups.id = groupmembers.group_id WHERE groupmembers.group_id IN (', groupIds, ') AND groupmembers.foreign_id = germinatebase.id) AS group_ids, ')), @SQL,' FROM phenotypedata LEFT JOIN germinatebase ON germinatebase.id = phenotypedata.germinatebase_id LEFT JOIN germinatebase g ON g.id = germinatebase.entityparent_id ', IF(groupIds IS NULL, '', 'LEFT JOIN groupmembers ON germinatebase.id = groupmembers.foreign_id') ,' LEFT JOIN datasets ON datasets.id = phenotypedata.dataset_id LEFT JOIN licenses ON licenses.id = datasets.license_id LEFT JOIN treatments ON treatments.id = phenotypedata.treatment_id WHERE (', IF(groupIds IS NULL AND markedIds IS NULL, '1=1', IF(groupIds IS NULL AND NOT(markedIds IS NULL), CONCAT('germinatebase.id IN (', markedIds, ')'), IF(markedIds IS NULL AND NOT(groupIds IS NULL), CONCAT('groupmembers.group_id IN (', groupIds, ')'), CONCAT('germinatebase.id IN (', markedIds, ') OR groupmembers.group_id IN (', groupIds, ')')))), ') AND datasets.id IN (', datasetIds, ') GROUP BY germinatebase.id, treatment_id, dataset_id, phenotypedata.location_id, recording_date, year'); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END