CREATE MATERIALIZED VIEW expression_feature_all AS
SELECT F.feature_id AS feature_id,
F.name AS feature_uniquename,
B.biomaterial_id AS biomaterial_id,
B.name AS biomaterial_name,
AN.analysis_id AS analysis_id,
AN.program AS analysis_method,
INNER JOIN element E ON E.element_id = ER.element_id
INNER JOIN feature F ON F.feature_id = E.feature_id
INNER JOIN quantification Q ON Q.quantification_id = ER.quantification_id
INNER JOIN acquisition AQ ON AQ.acquisition_id = Q.acquisition_id
INNER JOIN assay A ON A.assay_id = AQ.assay_id
INNER JOIN assay_biomaterial AB ON AB.assay_id = A.assay_id
INNER JOIN biomaterial B ON B.biomaterial_id = AB.biomaterial_id
INNER JOIN analysis AN ON AN.analysis_id = Q.analysis_id;
CREATE MATERIALIZED VIEW mview_phenotype AS
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,
method.cvterm_id AS method_id,
method.name AS method_name,
unit.cvterm_id AS unit_id,
s.stock_id AS germplasm_id,
s.name AS germplasm_name,
array_to_json(array_agg(p.value)) AS values
LEFT JOIN {cvterm} trait ON trait.cvterm_id=p.attr_id
LEFT JOIN {project} proj USING(project_id)
LEFT JOIN {cvterm} method ON method.cvterm_id=p.assay_id
LEFT JOIN {cvterm} unit ON unit.cvterm_id=p.unit_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 = 2944
LEFT JOIN {phenotypeprop} yr ON yr.phenotype_id=p.phenotype_id AND yr.type_id = 141