Find folders recursively from parent folder in PhotoSweeper X #sql #sqlite

query.sql

-- PhotoSweeper X database (sqlite3) is located in:
-- ~/Library/Containers/com.overmacs.photosweeperpaddle/Data/Library/Application Support/PhotoSweeper X/Library.pslib


-- First query gives all folderids that are subfolders of folderid 351
CREATE TABLE folders WITH RECURSIVE folds(x) AS (VALUES(351) UNION SELECT folderID FROM PSFolder, folds WHERE PSFolder.parentFolderID=folds.x) SELECT * FROM folds ORDER BY x;

-- If you're curious how many files are in this folder structure, run this
SELECT count(*) FROM PSFile WHERE parentFolderID IN folders;

-- Second command deletes all files that have parentFolderIDs in that list
DELETE FROM PSFile WHERE parentFolderID IN folders;

-- Make sure to delete the table
DROP TABLE folders;