I asked this question previously but this was for a two-layer tree and the solution I was given worked perfectly.
I now have a multi level tree (up to 3, but lets assume there could be more in the future.
My code currently looks like this:
SELECT * FROM fin_document AS finl
LEFT OUTER JOIN fin_document AS finl2
ON finl2.id = finl.parent_line_id
ORDER BY
CASE WHEN finl2.ordinal IS NULL THEN finl.ordinal
ELSE concat(finl2.ordinal,'-',finl.ordinal) END
Lets assume a similar tree as before:
(id) (Item) (#) (parent_line_id)
1234 - Car - 1 - null
0000 - Boat - 2 - null
2222 - House - 4 - null
6545 - Bike - 5 - null
6547 - Wheels - 0 - 1234
4442 - Bed - 1 - 2222
1474 - Sink - 0 - 2222
9456 - Tires - 0 - 6547 *New item, child of wheels
8975 - L.Nuts - 1 - 6547 *New item, child of wheels
oh and the # column is "ordinal"
So how would I get this to sort proper with more than one parent?
The proper sort should look like:
(id) (Item) (#) (parent_line_id)
1234 - Car - 1 - null
6547 - Wheels - 0 - 1234
9456 - Tires - 0 - 6547
8975 - L.Nuts - 1 - 6547
0000 - Boat - 2 - null
2222 - House - 4 - null
1474 - Sink - 0 - 2222
4442 - Bed - 1 - 2222
6545 - Bike - 5 - null
Note: I cannot alter the tables whatsoever. I am only able to pull data from the tables, as the tables are managed by another company, who's software we use. I'm aware that they will get more and more complex if there are more children, but I do not think there will be more than 3-4 children for what my company will be using this for. Unfortunately, due to this complexity, this is why I had to return here and ask again :(
Here's a dirty trick to handle arbitrary depth:
fiddle (not sure why column k is not displayed correctly. column k represents the sorting key, and is built up with similar format as your original query) Also, it takes exponential execution time. So it may not be what you want.