Difference between revisions of "Manual Phenotyping Database"

From Poland Lab Wiki
Jump to: navigation, search

Deprecated: The each() function is deprecated. This message will be suppressed on further calls in /home/customer/www/wiki.wheatgenetics.k-state.edu/public_html/includes/diff/DairikiDiff.php on line 434

Deprecated: assert(): Calling assert() with a string argument is deprecated in /home/customer/www/wiki.wheatgenetics.k-state.edu/public_html/includes/diff/DairikiDiff.php on line 437
(Added example row to column query for phenotype table.)
(updated query statement to sort columns in order by date)
 
(10 intermediate revisions by 2 users not shown)
Line 6: Line 6:
 
[[File:phenotyping.png]]
 
[[File:phenotyping.png]]
  
Example Queries
+
==Example Queries==
  
The query below is an example of how to generate a monthly report of the PCTHEAD (percent heading) trait.
+
===Row-to-Column Query===
  
 +
FOR QUERY OF REPEATED MEASUREMENTS:  This query can be used to extract a column format of a repeat measurements (over dates) for a single trait. 
 +
 +
The query below is an example of how to generate a report of the PCTHEAD (percent heading) trait from the 16ASH plots.
 +
 +
<syntaxhighlight lang="sql">
 
SET SESSION group_concat_max_len = 10000;
 
SET SESSION group_concat_max_len = 10000;
SET @sql = NULL;
+
SET @SQL = NULL;
 
SELECT
 
SELECT
 
   GROUP_CONCAT(DISTINCT
 
   GROUP_CONCAT(DISTINCT
 
     CONCAT(
 
     CONCAT(
 
       'MAX(IF(phenotype_date = ''', `phenotype_date`, ''',phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
 
       'MAX(IF(phenotype_date = ''', `phenotype_date`, ''',phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
  ) INTO @sql
+
Order by `phenotype_date` ASC ) INTO @SQL FROM phenotype WHERE YEAR(phenotype_date) = 2016;
FROM phenotype WHERE YEAR(phenotype_date) = 2016;
+
SET @SQL = CONCAT('SELECT  entity_id, ', @SQL, '
SET @sql = CONCAT('SELECT  entity_id, ', @sql, '  
+
 
                   FROM    phenotype WHERE phenotype.entity_id LIKE ''%16ASH0%'' AND phenotype.trait_id LIKE ''%PCTHEAD%''
 
                   FROM    phenotype WHERE phenotype.entity_id LIKE ''%16ASH0%'' AND phenotype.trait_id LIKE ''%PCTHEAD%''
                   GROUP  BY entity_id');
+
                   GROUP  BY entity_id' );
PREPARE stmt FROM @sql;
+
PREPARE stmt FROM @SQL;
 
EXECUTE stmt;
 
EXECUTE stmt;
 
DEALLOCATE PREPARE stmt;
 
DEALLOCATE PREPARE stmt;
 +
</syntaxhighlight>

Latest revision as of 19:25, 7 June 2017

The manual phenotyping database contains phenotypic field data that was collected by hand.

The main tables in the manual phenotyping database are shown in the figure below.


Phenotyping.png

Example Queries

Row-to-Column Query

FOR QUERY OF REPEATED MEASUREMENTS: This query can be used to extract a column format of a repeat measurements (over dates) for a single trait.

The query below is an example of how to generate a report of the PCTHEAD (percent heading) trait from the 16ASH plots.

SET SESSION group_concat_max_len = 10000;
SET @SQL = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(phenotype_date = ''', `phenotype_date`, ''',phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
 ORDER BY `phenotype_date` ASC ) INTO @SQL FROM phenotype WHERE YEAR(phenotype_date) = 2016;
SET @SQL = CONCAT('SELECT  entity_id, ', @SQL, '
                  FROM    phenotype WHERE phenotype.entity_id LIKE ''%16ASH0%'' AND phenotype.trait_id LIKE ''%PCTHEAD%''
                  GROUP   BY entity_id' );
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;