Week 07 ‐ Devlog
Running patterns against FirstFrost data, using ILIKE search, IS NULL audit, and three JOIN types.
ILIKE case-insensitive search:
SELECT common_name, variety, current_stage
FROM public.plants
WHERE common_name ILIKE '%to%';
IS NULL audit (plants with no data logs):
SELECT p.common_name, p.variety, p.current_stage
FROM public.plants p
LEFT JOIN public.data_logs dl ON p.id = dl.plant_id
WHERE dl.id IS NULL;
INNER JOIN (plants with active logs):
SELECT p.common_name, dl.metric_type, dl.value, dl.unit, dl.logged_at
FROM public.plants p
INNER JOIN public.data_logs dl ON p.id = dl.plant_id
ORDER BY dl.logged_at DESC
LIMIT 5;
FULL OUTER JOIN (surface the SET NULL orphan test):
SELECT p.common_name, t.title, t.status, t.plant_id
FROM public.plants p
FULL OUTER JOIN public.tasks t ON p.id = t.plant_id
ORDER BY p.common_name NULLS LAST;
ILIKE caught Tomato with %to% case-insensitivity. For the second block, neither the remaining plants (tomato & basil) have data logs from the 100K seed so they will not show up as unlogged. That is correct, given the dataset we have.
For the INNER JOIN, both plants are showing up with recent logs across multiple metric types (due to the seeded data) which means INNER JOIN is working correctly - the only plants returned should be those with matching data_logs.
And lastly, FULL OUTER JOIN block shows the orphaned task of "Check pH levels" and has a NULL plant_id. The plant was deleted, but the task survives. The last query surfaces all three cases in one query.
- All three JOIN types confirmed working - INNER JOIN returned only plants with logs, FULL OUTER JOIN surfaced the orphaned "Check pH levels" task with
plant_idNULL. - The SET NULL behavior from Issue #6 paid off here as a legitimate data integrity story rather than a manufactured demo.
ILIKE search
INNERJOIN plants
FULLOUTERJOIN orphan
