Query a nested data structure for locations

Question

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?

Ajax1234 · Accepted Answer · 2022-09-13 13:39:33Z

1

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;

See fiddle.

answered Sep 13, 2022 at 13:39

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query a nested data structure for locations

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related