PermaplanT

-- -- Example: Relationships

-- For this example we use the following notation -- companion "<->" -- antagonist ">-<" -- Consider the following relationships between plants: -- Radish <-> carrot -- Radish <-> potato -- Carrot >-< potato -- Marigold <-> carrot -- Marigold <-> radish

-- We want to plant carrots and radish and get suggestions for it. -- When looking for companions we would expect the result just to be marigold. -- Potato wouldn't be in the result since it's an antagonist to carrot.

-- Let's insert the plants. -- insert carrot WITH carrot_family AS ( INSERT INTO taxons (rank, name, parent_id) VALUES ('family', 'Apiaceae', NULL) RETURNING id ),

carrot_subfamily AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'subfamily', 'Apioideae', carrot_family.id FROM carrot_family RETURNING id ),

carrot_genus AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'genus', 'Daucus', carrot_subfamily.id FROM carrot_subfamily RETURNING id ),

carrot_species AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'species', 'Daucus carota', carrot_genus.id FROM carrot_genus RETURNING id ),

carrot AS ( INSERT INTO plants (binomial_name, common_name, family_id, subfamily_id, genus_id, species_id) SELECT 'Daucus carota', '{"Carrot"}', (Select id from carrot_family), (SELECT id FROM carrot_subfamily), (SELECT id from carrot_genus), (SELECT id from carrot_species) RETURNING id ),

-- insert radish radish_family AS ( INSERT INTO taxons (rank, name, parent_id) VALUES ('family', 'Brassicaceae', NULL) RETURNING id ),

radish_genus AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'genus', 'Raphanus', radish_family.id FROM radish_family RETURNING id ),

radish_species AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'species', 'Raphanus raphanistrum', radish_genus.id FROM radish_genus RETURNING id ),

radish AS ( INSERT INTO plants (binomial_name, common_name, family_id, genus_id, species_id) SELECT 'Raphanus raphanistrum', '{"Radish"}', (Select id from radish_family), (SELECT id from radish_genus), (SELECT id from radish_species) RETURNING id ),

-- insert marigold marigold_family AS ( INSERT INTO taxons (rank, name, parent_id) VALUES ('family', 'Asteraceae', NULL) RETURNING id ),

marigold_subfamily AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'subfamily', 'Asteroideae', marigold_family.id FROM marigold_family RETURNING id ),

marigold_genus AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'genus', 'Calendula', marigold_subfamily.id FROM marigold_subfamily RETURNING id ),

marigold_species AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'species', 'Calendula officinalis', marigold_genus.id FROM marigold_genus RETURNING id ),

marigold AS ( INSERT INTO plants (binomial_name, common_name, family_id, subfamily_id, genus_id, species_id) SELECT 'Calendula officinalis', '{"Marigold"}', (Select id from marigold_family), (SELECT id FROM marigold_subfamily), (SELECT id from marigold_genus), (SELECT id from marigold_species) RETURNING id ),

-- insert potato potato_family AS ( INSERT INTO taxons (rank, name, parent_id) VALUES ('family', 'Solanaceae', NULL) RETURNING id ),

potato_subfamily AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'subfamily', 'Solanoideae', potato_family.id FROM potato_family RETURNING id ),

potato_genus AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'genus', 'Solanum', potato_subfamily.id FROM potato_subfamily RETURNING id ),

potato_species AS ( INSERT INTO taxons (rank, name, parent_id) SELECT 'species', 'Solanum tuberosum', potato_genus.id FROM potato_genus RETURNING id ),

potato AS ( INSERT INTO plants (binomial_name, common_name, family_id, subfamily_id, genus_id, species_id) SELECT 'Solanum tuberosum', '{"Potato"}', (Select id from potato_family), (SELECT id FROM potato_subfamily), (SELECT id from potato_genus), (SELECT id from potato_species) FROM potato_species RETURNING id )

INSERT INTO taxon_relationships (kind, strength, left_taxon_id, right_taxon_id) -- Radish <-> carrot VALUES ('companion', 1, (SELECT id FROM radish_species), (SELECT id FROM carrot_species)), -- Radish <-> potato ('companion', 1, (SELECT id FROM radish_species), (SELECT id FROM potato_species)), -- Carrot >-< potato ('antagonist', 1, (SELECT id FROM carrot_species), (SELECT id FROM potato_species)), -- Marigold <-> carrot ('companion', 1, (SELECT id FROM marigold_species), (SELECT id FROM carrot_species)), -- Marigold <-> radish ('companion', 1, (SELECT id FROM marigold_species), (SELECT id FROM radish_species));

-- Let's look for companions that go well with carrot and radish

-- First we get related plants which are not in the set we already have WITH potential_companions AS ( SELECT r1.left_taxon_id AS taxon_id, r1.kind FROM taxon_relationships AS r1 WHERE r1.right_taxon_id IN (3, 7) AND r1.left_taxon_id NOT IN (3, 7) UNION SELECT r2.right_taxon_id AS taxon_id, r2.kind FROM taxon_relationships AS r2 WHERE r2.left_taxon_id IN (3, 7) AND r2.right_taxon_id NOT IN (5, 7) )

SELECT * FROM potential_companions companions LEFT JOIN taxons AS t ON companions.taxon_id = t.id LEFT JOIN plants AS p ON t.id IN (p.family_id, p.subfamily_id, p.genus_id, p.species_id) -- Then we need to remove companions are antagonists as well WHERE kind = 'companion' AND NOT EXISTS ( SELECT 1 FROM potential_companions antagonists WHERE kind = 'antagonist' AND companions.taxon_id = antagonists.taxon_id );

-- -- Example: Hierarchical information

-- Let's set an for the family of potato and one for the carrot species. UPDATE taxons SET icon_url = '/assets/img/solanaceae.png' WHERE name = 'Solanaceae'; UPDATE taxons SET icon_url = '/assets/img/daucus_carota.png' WHERE name = 'Daucus carota'; -- Get all plants with their hierarchy information SELECT plants.common_name, species.name, genus.name, subfamily.name, family.name, COALESCE(species.icon_url, genus.icon_url, subfamily.icon_url, family.icon_url) AS icon_url FROM plants LEFT JOIN taxons AS species ON (species.rank = 'species' AND species.id = plants.species_id) LEFT JOIN taxons AS genus ON (genus.rank = 'genus' AND genus.id = plants.genus_id) LEFT JOIN taxons AS subfamily ON (subfamily.rank = 'subfamily' AND subfamily.id = plants.subfamily_id) LEFT JOIN taxons AS family ON (family.rank = 'family' AND family.id = plants.family_id);

-- -- Example: Varieties

-- Let's say carrots have a height of 0.3m and a white flower colour. UPDATE plants SET mature_size_height = '0.3', flower_colour = 'white' WHERE binomial_name = 'Daucus carota';

-- There is a variety of it which grows higher but has the same flower_colour. INSERT INTO varieties (plant_id, mature_size_height) VALUES ((SELECT id FROM plants WHERE binomial_name = 'Daucus carota'), '0.5');

-- And another variety which has the same height but yellow flowers. INSERT INTO varieties (plant_id, flower_colour) VALUES ((SELECT id FROM plants WHERE binomial_name = 'Daucus carota'), 'yellow');

-- Get all plants without varieties. SELECT id, binomial_name, mature_size_height, flower_colour FROM plants;

-- Get all varieties. SELECT v.id, p.binomial_name, COALESCE(v.mature_size_height, p.mature_size_height), COALESCE(v.flower_colour, p.flower_colour) FROM varieties AS v LEFT JOIN plants p ON v.plant_id = p.id;

-- Get all plants and varieties. SELECT p.id, p.binomial_name, p.mature_size_height, p.flower_colour FROM plants AS p UNION SELECT v.id, p.binomial_name, COALESCE(v.mature_size_height, p.mature_size_height), COALESCE(v.flower_colour, p.flower_colour) FROM varieties AS v LEFT JOIN plants p ON v.plant_id = p.id;