SQLite recursive query to return file path

Question

At the moment I'm developing an application which has to store a file hierarchy (folders and files are not distinguished) in a database. For this created following table:

tbl_files
----------------------------------
|   id   |   name   |   parent   |
----------------------------------

The parent field is a foreign to tbl_files itself. The main directory has the id root. I now want to get the path to a file until the root directory is reached. I thought about to do this with a recursive SQL-Query but I do not know how to "return" the path from the database.

Is a recursive query okay or is it bad practice? And how should I "generate" the path with this query?

Answer 1

To get the path entries, use a recursive CTE. To combine the names, use group_concat():

WITH RECURSIVE path(level, name, parent) AS (
    SELECT 0, name, parent
    FROM tbl_files
    WHERE id = @MyFileID
    UNION ALL
    SELECT path.level + 1,
           tbl_files.name,
           tbl_files.parent
    FROM tbl_files
    JOIN path ON tbl_files.id = path.parent
),
path_from_root AS (
    SELECT name
    FROM path
    ORDER BY level DESC
)
SELECT group_concat(name, '/')
FROM path_from_root;