1 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Hierarchical queries

Handy tip for writing reports. If you need to do a query on hierarchical data (e.g. locations, taxa, samples etc) then you can use a recursive query in the report file. Here's an example which loads a list of samples for a given location_id, including the child samples if the parent is linked to the location:

WITH RECURSIVE search_samples(top_id, id, location_id, survey_id, date_start, date_end, depth) AS (
        SELECT s.id, s.id, s.location_id, s.survey_id, s.date_start, s.date_end, 1
        FROM samples s
        WHERE s.deleted=false
      UNION ALL
        SELECT ss.id, s.id, ss.location_id, ss.survey_id, s.date_start, s.date_end, ss.depth+1
        FROM samples s, search_samples ss
        WHERE s.parent_id = ss.id AND s.deleted=false
      )
      select * from search_samples where location_id=502
John van Breda
Biodiverse IT

johnvanbreda's Website