search

UofS-Pulse-Binfo / analyzedphenotypes

Tripal/Drupal support for analyzed phenotypic data including data loaders, exporters, trait pages and summaries on germplasm pages.
GNU General Public License v2.0
0 stars 1 forks source link

Phenotype MView is incorrectly determining the species. #48

Closed laceysanderson closed 5 years ago

laceysanderson commented 5 years ago

The Data summary does not show what I expect when sync'd after Data Seeding but rather shows a ridiculous number of genus.

screen shot 2018-11-27 at 3 00 18 pm

This appears to be a problem in the mview_phenotype materialized view.

laceysanderson commented 5 years ago

Current Mview Query:

    SELECT
      o.genus         AS organism_genus,
      trait.cvterm_id AS trait_id,
      trait.name      AS trait_name,
      proj.project_id AS project_id,
      proj.name       AS project_name,
      loc.value       AS location,
      yr.value        AS year,
      s.stock_id      AS germplasm_id,
      s.name          AS germplasm_name,
      avg( CAST(p.value as FLOAT) ) AS mean
    FROM {phenotype} p
      LEFT JOIN {cvterm} trait ON trait.cvterm_id=p.attr_id
      LEFT JOIN {project} proj USING(project_id)
      LEFT JOIN {stock} s USING(stock_id)
      LEFT JOIN {organism} o ON o.organism_id=s.organism_id
      LEFT JOIN {phenotypeprop} loc ON loc.phenotype_id=p.phenotype_id AND loc.type_id = 2940
      LEFT JOIN {phenotypeprop} yr ON yr.phenotype_id=p.phenotype_id AND yr.type_id = 141
    GROUP BY
      trait.cvterm_id,
      trait.name,
      proj.project_id,
      proj.name,
      loc.value,
      yr.value,
      s.stock_id,
      s.name,
      o.genus

with the following results (organism_genus should be consistently Tripalus):

 organism_genus | trait_id |   trait_name   | experiment_id | experiment_name |                location                 | year | stock_id |   stock_name   |  mean   
----------------+----------+----------------+---------------+-----------------+-----------------------------------------+------+----------+----------------+---------
 voluptatem     |     6759 | Angelina Kuhic |            31 | dolorem         | West Darylmouth, Western Sahara         | 1999 |     1640 | explicabo      |     500
 incidunt       |     6759 | Angelina Kuhic |            31 | dolorem         | West Darylmouth, Western Sahara         | 1999 |     1624 | minus          | 516.667
 labore         |     6756 | Reva Kunde     |            31 | dolorem         | South Jarrett, Micronesia               | 1973 |     1847 | numquam        | 595.667
 reprehenderit  |     6756 | Reva Kunde     |            31 | dolorem         | Eichmannville, Australia                | 1974 |     1589 | molestiae      | 587.333
 a              |     6759 | Angelina Kuhic |            31 | dolorem         | West Sybleton, Azerbaijan               | 2000 |     1752 | et             | 562.333
 voluptates     |     6756 | Reva Kunde     |            31 | dolorem         | Eichmannville, Australia                | 1974 |     1665 | optio          | 633.333
 occaecati      |     6753 | Jaida Simonis  |            31 | dolorem         | New Maudborough, Mexico                 | 2013 |     1738 | magnam         | 597.333
 distinctio     |     6756 | Reva Kunde     |            31 | dolorem         | Lake Aurelia, Kiribati                  | 1973 |     1630 | cupiditate     |     651
 quia           |     6756 | Reva Kunde     |            31 | dolorem         | Niahaven, Switzerland                   | 1974 |     1826 | omnis          |     624
 voluptatem     |     6753 | Jaida Simonis  |            31 | dolorem         | New Demario, Bahamas                    | 2011 |     1819 | quam           | 515.667
 ipsam          |     6753 | Jaida Simonis  |            31 | dolorem         | New Demario, Bahamas                    | 2011 |     1717 | soluta         | 677.667
 aut            |     6759 | Angelina Kuhic |            31 | dolorem         | Terrytown, Puerto Rico                  | 1998 |     1787 | sed            |     474
 culpa          |     6753 | Jaida Simonis  |            31 | dolorem         | New Maudborough, Mexico                 | 2013 |     1793 | pariatur       |     549
 esse           |     6756 | Reva Kunde     |            31 | dolorem         | East Barneyberg, Libyan Arab Jamahiriya | 1972 |     1634 | incidunt       | 614.333
 itaque         |     6759 | Angelina Kuhic |            31 | dolorem         | West Sybleton, Azerbaijan               | 2000 |     1712 | vero           | 462.333
 eum            |     6756 | Reva Kunde     |            31 | dolorem         | Mikaylafurt, Monaco                     | 1972 |     1608 | dicta          | 560.667
 blanditiis     |     6753 | Jaida Simonis  |            31 | dolorem         | Dietrichtown, Oman                      | 2012 |     1668 | aliquam        |     583
 aut            |     6756 | Reva Kunde     |            31 | dolorem         | South Jarrett, Micronesia               | 1973 |     1621 | eius           |     665
 blanditiis     |     6759 | Angelina Kuhic |            31 | dolorem         | North Cleta, Eritrea                    | 1998 |     1668 | aliquam        | 527.333
 inventore      |     6756 | Reva Kunde     |            31 | dolorem         | South Jarrett, Micronesia               | 1973 |     1613 | fuga           | 602.667
 tenetur        |     6756 | Reva Kunde     |            31 | dolorem         | East Barneyberg, Libyan Arab Jamahiriya | 1972 |     1777 | fugit          |     551
 soluta         |     6756 | Reva Kunde     |            31 | dolorem         | South Jarrett, Micronesia               | 1973 |     1686 | qui            |     605
 sint           |     6753 | Jaida Simonis  |            31 | dolorem         | Dietrichtown, Oman                      | 2012 |     1761 | dolores        |     504
 doloribus      |     6756 | Reva Kunde     |            31 | dolorem         | Mikaylafurt, Monaco                     | 1972 |     1749 | voluptatem     | 624.333
 sint           |     6759 | Angelina Kuhic |            31 | dolorem         | North Cleta, Eritrea                    | 1998 |     1761 | dolores        | 440.667
 ut             |     6756 | Reva Kunde     |            31 | dolorem         | Eichmannville, Australia                | 1974 |     1610 | hic            | 622.333
 dolorem        |     6756 | Reva Kunde     |            31 | dolorem         | Anjalibury, Guernsey                    | 1971 |     1875 | rerum          | 656.333
 distinctio     |     6759 | Angelina Kuhic |            31 | dolorem         | Beattymouth, San Marino                 | 1999 |     1813 | non            |     540
 natus          |     6756 | Reva Kunde     |            31 | dolorem         | Anjalibury, Guernsey                    | 1971 |     1871 | sed            |     593
 quam           |     6756 | Reva Kunde     |            31 | dolorem         | Lake Aurelia, Kiribati                  | 1973 |     1811 | laborum        |     566
 sit            |     6756 | Reva Kunde     |            31 | dolorem         | South Jarrett, Micronesia               | 1973 |     1773 | ut             |     620
 soluta         |     6759 | Angelina Kuhic |            31 | dolorem         | Terrytown, Puerto Rico                  | 1998 |     1705 | voluptas       |     545
 distinctio     |     6756 | Reva Kunde     |            31 | dolorem         | Mikaylafurt, Monaco                     | 1972 |     1713 | quo            |     594
 itaque         |     6756 | Reva Kunde     |            31 | dolorem         | South Jarrett, Micronesia               | 1973 |     1886 | quo            | 608.667
 maiores        |     6756 | Reva Kunde     |            31 | dolorem         | Niahaven, Switzerland                   | 1974 |     1796 | voluptates     | 625.667
 ex             |     6753 | Jaida Simonis  |            31 | dolorem         | New Maudborough, Mexico                 | 2013 |     1746 | omnis          | 513.333
 eos            |     6753 | Jaida Simonis  |            31 | dolorem         | New Demario, Bahamas                    | 2011 |     1799 | vitae          | 479.333
laceysanderson commented 5 years ago

It seems this was due to a bug in the phenotype database seeder where the stocks were not all assigned the same organism. Since the materialized views pull the genus from the stocks, this caused the symptom of multiple genus.