Concatenating arbitrary number of rows of strings in mysql (hierarchical query)

Question

I have a mysql table with albums. Each album can be a top level album, or a child album of another album. Each album has a foldername which is the name of the folder its pictures are in. Each album also has a field called parent which is the id of the parent album. So, if I have a path to an image like this:

root/album1/album2/image1.jpg

then the album table in the database will look like this:

id parent foldername
1  NULL   root
2  1      album1
3  2      album2

The question is then, how do I get the path printed earlier from this table with only mysql?

Answer 1

This article explains a way to do it: linky

Answer 2

Totally untested and typed off the top of my head...

DECLARE @FOLDER VARCHAR(200)
DECLARE @TOPID AS INT
DECLARE @MYID As int

CREATE TABLE #tmp
(
    [id] INT,
    [path] VARCHAR(50)
)

DECLARE tempCursor CURSOR FOR SELECT id FROM albums ORDER BY id
OPEN tempCursor

FETCH NEXT FROM tempCursor INTO @TOPID

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FOLDER = ''
    SET @MYID = @TOPID

    WHILE @MYID is not null
    BEGIN
        SELECT @MYFOLDER = foldername FROM albums WHERE id = @MYID
        SET @FOLDER = @MYFOLDER + '/' + @FOLDER
        SELECT @MYID = parent FROM albums WHERE id = @MYID
    END

    INSERT INTO #tmp
    SELECT @TOPID, @FOLDER

    FETCH NEXT FROM tempCursor INTO @TOPID
END
CLOSE tempCursor
DEALLOCATE tempCursor

SELECT * FROM #tmp
DROP TABLE #tmp

That should at least give you an idea how to get your path names. You never specified where your file names were stored.

BTW, this is gonna be slow. I hate using cursors...

Answer 3

I'm not sure storing a tree in Database is a good idea...

To keep your problem simple maybe just store the full path of an album in a column of your table...

id parent path           foldername
1  NULL   /              root
2  1      /root/         album1
3  2      /root/album1/  album2

Answer 4

DB2, SQL Server, PostgreSQL, and Oracle all support Common Table Expressions (CTE), which can be used to accomplish this. Oracle also has the "CONNECT BY" keyword.

I know that doesn't solve your problem, but maybe it will help someone else looking for a solution on something other than MySQL.

In your case, for performance sake, I recommend storing the full path in a column--the management of the denormalized data on insert/update will likely pay itself back many times in performance on reads.