user1775888 user1775888 - 3 months ago 13
SQL Question

How to sort the data order by a field cross/exist in two table

How to sorting data order by a field cross/exist in two table?

the sequence exist in folder table and file table, how to sort them together

query

SELECT * FROM folder fo
LEFT JOIN file fi ON fi.parent_folder_id = fo.id
WHERE fo.parent_folder_id = $1 AND fi.parent_folder_id = $1
ORDER BY fo.sequence fi.sequence ?? ; << my problem

[1]


data example

folder
id | sequence | parent_folder_id | name
1 | 0 | | root
2 | 0 | 1 |
3 | 2 | 1 |

file
id | sequence | parent_folder_id |
1 | 1 | 1 |


output

folder(id:1, sequence:0 name:root)
folder(id:2, sequence:0)
file(id:1, sequence:1)
folder(id:3 sequence:2)

Answer

If you want to list all files and folders under first level of a given folder, then I recommend you use UNION ALL and get list of files and list of folders apart:

(
    SELECT 'folder' AS type, fo.id, fo.sequence, fo.name
    FROM folder fo
    WHERE fo.parent_folder_id = 1
)
UNION ALL
(
    SELECT 'file' AS type, fi.id, fi.sequence, NULL
    FROM file fi
    WHERE fi.parent_folder_id = 1
)
ORDER BY sequence;

It is odd that you don't have name in files table, but I think it is probably just oversimplification of the problem. If you also want to include the parent folder in the listing, I'd just add it as another UNION ALL subquery with fixed lower possible sequence:

(
    SELECT 'folder' AS type, fo.id, -1 AS sequence, fo.name
    FROM folder fo
    WHERE fo.id = 1
)
UNION ALL
(
    SELECT 'folder' AS type, fo.id, fo.sequence, fo.name
    FROM folder fo
    WHERE fo.parent_folder_id = 1
)
UNION ALL
(
    SELECT 'file' AS type, fi.id, fi.sequence, NULL
    FROM file fi
    WHERE fi.parent_folder_id = 1
)
ORDER BY sequence;
Comments