Dylan Chen Dylan Chen - 6 months ago 10
SQL Question

Find full path of a file in a file system

I have a file system like this:

C Drive - Docements - (empty)
- Music - Rock - a.mp3
- Jazz - SmoothJazz - b.mp3
- Photo - (empty)
D Drive - (empty)


Every file or directory has an id, and its parent's id. Now given a file name or directory name, how do you find the full path of it?

My algorithm is:

Recursively (
if (parent id is not null)
find parent id in the table
)

Answer

You can use recursive query for this. Assuming a table file with columns id, name, parent:

WITH RECURSIVE t AS
(SELECT id, name, parent, name as path
FROM file
WHERE id=3
UNION ALL
SELECT si.id,si.name,
si.parent,
si.name || '/' || sp.path as path
FROM file As si
INNER JOIN t AS sp
ON (si.id = sp.parent)
)
SELECT *
FROM t where parent is null
ORDER BY path

This will give you the whole path in path to the file with id=3. Might not be the most efficient query.