40 lines
805 B
MySQL
40 lines
805 B
MySQL
|
WITH RECURSIVE folder_hierarchy AS (
|
||
|
|
-- Start with the given directory
|
||
|
|
SELECT
|
||
|
|
folder_id
|
||
|
|
FROM
|
||
|
|
folders
|
||
|
|
WHERE
|
||
|
|
folder_id = $1
|
||
|
|
|
||
|
|
UNION ALL
|
||
|
|
|
||
|
|
-- Recursively find all subdirectories
|
||
|
|
SELECT
|
||
|
|
f.folder_id
|
||
|
|
FROM
|
||
|
|
folders f
|
||
|
|
INNER JOIN
|
||
|
|
folder_hierarchy fh ON f.parent_folder_id = fh.folder_id
|
||
|
|
),
|
||
|
|
deleted_files AS (
|
||
|
|
-- Delete the files and return their IDs
|
||
|
|
DELETE FROM
|
||
|
|
files
|
||
|
|
WHERE
|
||
|
|
folder_id IN (SELECT folder_id FROM folder_hierarchy)
|
||
|
|
RETURNING file_id
|
||
|
|
),
|
||
|
|
deleted_folders AS (
|
||
|
|
-- Delete the directories
|
||
|
|
DELETE FROM
|
||
|
|
folders
|
||
|
|
WHERE
|
||
|
|
folder_id IN (SELECT folder_id FROM folder_hierarchy)
|
||
|
|
)
|
||
|
|
-- Return the IDs of deleted files
|
||
|
|
SELECT
|
||
|
|
file_id
|
||
|
|
FROM
|
||
|
|
deleted_files;
|