I have this locations table:
| id | type | slug | parent_location_id |
|---|---|---|---|
| 1 | Country | Germany | nil |
| 2 | FederalState | Hessen | 1 |
| 3 | City | Frankfurt | 2 |
| 4 | Street | Example | 3 |
I have a tags table which has a belongs_to logic to the locations table:
| id | name | location_id |
|---|---|---|
| 1 | blue | 1 |
| 2 | yellow | 2 |
| 3 | red | 2 |
| 4 | orange | 3 |
| 5 | white | 4 |
| 6 | black | 4 |
All tags of a location are displayed on webpages with this URL schema:
- http://localhost:4000/Germany
- http://localhost:4000/Germany/Hessen
- http://localhost:4000/Germany/Hessen/Frankfurt
- http://localhost:4000/Germany/Hessen/Frankfurt/Example
- http://localhost:4000/:country_slug/:federal_state_slug/:city_slug/:street_slug
To fetch all the tags for the last URL which would include all the tags for the Country, the FederalState, the City and the Street I run down the tree and query for the :country_slug first, then for the :federal_state_slug, then for the :city_slug and lastly for the :street_slug. Always in combination with the parent_location_id to make sure that it makes sense within the location tree. After that I combine all the location ids and run a select on tags. Needless to say that this takes for ever since I run 5 SQL queries in the worst case (for a street) and 2 in the best case (for a country).
I can't help but think that there must be a better data structure (I am happy to restructure the database) or some SQL magic so get this done with one SQL query. Is there?
CodePudding user response：
You can use a recursive cte, in which you join your tags table onto each recursive query. The cte builds a JSON array of tag ids, which you can query later on:
with recursive cte(id, url, tag_path) as (
select l.id, 'http://localhost:4000/'||l.slug, ('['||t.id||']')::jsonb
from locations l join tags t on t.location_id = l.id where l.parent_location_id is null
union all
select l.id, c.url||'/'||l.slug, c.tag_path||(t.id::text::jsonb)
from locations l join cte c on c.id = l.parent_location_id
join tags t on t.location_id = l.id
)
select * from cte;