materialized_phenoview

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

breeding_program_id

int4

10

√

null

location_id

int4

10

√

null

year_id

text

2147483647

√

null

trial_id

int4

10

√

null

accession_id

int4

10

√

null

seedlot_id

int4

10

√

null

stock_id

int4

10

√

null

stock.stock_id

Implied Constraint

R

phenotype_id

int4

10

√

null

phenotype.phenotype_id

Implied Constraint

R

trait_id

int4

10

√

null

Relationships

Close relationships within degrees of separation

View Definition

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;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views