materialized_phenoview

SELECT breeding_program.project_id AS breeding_program_id,

    (location.value)::integer AS location_id,

    year.value AS year_id,

    trial.project_id AS trial_id,

    accession.stock_id AS accession_id,

    seedlot.stock_id AS seedlot_id,

    stock.stock_id,

    phenotype.phenotype_id,

    phenotype.cvalue_id AS trait_id

   FROM (((((((((((((stock accession

     LEFT JOIN stock_relationship ON (((accession.stock_id = stock_relationship.object_id) AND (stock_relationship.type_id IN ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE (((cvterm.name)::text = 'plot_of'::text) OR ((cvterm.name)::text = 'plant_of'::text) OR ((cvterm.name)::text = 'analysis_of'::text)))))))

     LEFT JOIN stock ON (((stock_relationship.subject_id = stock.stock_id) AND (stock.type_id IN ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE (((cvterm.name)::text = 'plot'::text) OR ((cvterm.name)::text = 'plant'::text) OR ((cvterm.name)::text = 'analysis_instance'::text)))))))

     LEFT JOIN stock_relationship seedlot_relationship ON (((stock.stock_id = seedlot_relationship.subject_id) AND (seedlot_relationship.type_id IN ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = 'seed transaction'::text))))))

     LEFT JOIN stock seedlot ON (((seedlot_relationship.object_id = seedlot.stock_id) AND (seedlot.type_id IN ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = 'seedlot'::text))))))

     LEFT JOIN nd_experiment_stock ON (((stock.stock_id = nd_experiment_stock.stock_id) AND (nd_experiment_stock.type_id IN ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = ANY (ARRAY[('phenotyping_experiment'::character varying)::text, ('field_layout'::character varying)::text, ('analysis_experiment'::character varying)::text])))))))

     LEFT JOIN nd_experiment_project ON ((nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id)))

     FULL JOIN project trial ON ((nd_experiment_project.project_id = trial.project_id)))

     LEFT JOIN project_relationship ON (((trial.project_id = project_relationship.subject_project_id) AND (project_relationship.type_id = ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = 'breeding_program_trial_relationship'::text))))))

     FULL JOIN project breeding_program ON ((project_relationship.object_project_id = breeding_program.project_id)))

     LEFT JOIN projectprop location ON (((trial.project_id = location.project_id) AND (location.type_id = ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = 'project location'::text))))))

     LEFT JOIN projectprop year ON (((trial.project_id = year.project_id) AND (year.type_id = ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = 'project year'::text))))))

     LEFT JOIN nd_experiment_phenotype ON ((nd_experiment_stock.nd_experiment_id = nd_experiment_phenotype.nd_experiment_id)))

     LEFT JOIN phenotype ON ((nd_experiment_phenotype.phenotype_id = phenotype.phenotype_id)))

  WHERE (accession.type_id = ( SELECT cvterm.cvterm_id

           FROM cvterm

          WHERE ((cvterm.name)::text = 'accession'::text)))

  ORDER BY breeding_program.project_id, (location.value)::integer, trial.project_id, accession.stock_id, seedlot.stock_id, stock.stock_id, phenotype.phenotype_id, phenotype.cvalue_id;