SQLite recursive query to return file path

Question

I am currently developing an application that should store a hierarchy of files (folders and files do not differ) in the database. The following table has been created for this:

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

The fields of the parent field are external to tbl_files. The main directory has id root. Now I want to get the file path until the root directory is reached. I thought about this with recursive SQL-Query, but I don’t know how to “return” the path from the database.

Is a recursive query ok or is it bad practice? And how do I "generate" a path with this query?

Answer 1

, CTE. , 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;