Example of using Postgres for Recursive Tree SQL in a Rails app
Example application for demonstrating tree SQL
Obviously:
bundle
Edit config/database.yml
as needed, then run:
rake db:setup db:test:prepare
Keep the tests passing:
rake
After seeding the database, you can run rails dbconsole
and play with
the following query:
WITH RECURSIVE search_tree(id, name, path) AS (
SELECT id, name, ARRAY[id]
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT categories.id, categories.name, path || categories.id
FROM search_tree
JOIN categories ON categories.parent_id=search_tree.id
WHERE NOT categories.id = ANY(path)
)
SELECT * FROM search_tree
ORDER BY path
;