Uncategorized

Today’s hairy SQL – path to a folder

Given a Folders table that has:

id, parent_id, name

The challenge is to create a path such as “/folder1/folder2/folder3” given you have the id for “folder3”.

We start with a recursive query:

WITH RECURSIVE folder_path (path, parent_id) AS (
SELECT name, parent_id FROM Folders WHERE id = $1
UNION ALL
SELECT Folders.name, Folders.parent_id
FROM Folders, folder_path
WHERE Folders.id = folder_path.parent_id
)

This gives us our path. Now we have to concatenate it. Postgresql (at least 8.4 which I’m using right now) can only concatenate arrays, not result sets, so we get:

SELECT array_to_string(array_agg(path), '/') FROM folder_path

The problem is this is in the wrong order – our recursive SQL started at the current path and worked its way upwards to the root, so our first row becomes the current path, and last row is the top level folder.

How can we reverse it? Pg 8.4 doesn’t have a way to reverse an array. Nor does it have a way to reverse strings (because we could reverse each string and then reverse the entire path).

But what we can do is add an incrementing id to our WITH RECURSIVE expression, and then create another WITH query that orders based on that column. I ended up with this:

WITH RECURSIVE folder_path (path, parent_id, id) AS (
SELECT name, parent_id, 1 FROM Folders WHERE id = $1
UNION ALL
SELECT Folders.name, Folders.parent_id, folder_path.id + 1
FROM Folders, folder_path
WHERE Folders.id = folder_path.parent_id
), reverse_folder_path AS (
SELECT * FROM folder_path ORDER BY id DESC
)
SELECT '/' || array_to_string(array_agg(path), '/') from reverse_folder_path;

Hope that’s useful to someone out there!

Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s