20 lines
469 B
MySQL
20 lines
469 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
|
||
|
|
)
|
||
|
|
DELETE FROM permissions WHERE user_id = $2 AND folder_id IN (SELECT folder_id FROM folder_hierarchy)
|