CODEWITHSUNDEEP
sqlsql-servert-sql
Tejas
Tejas

Reputation: 224

Recycle Bin for Web-Based File Explorer

I am creating a web-based file explorer for document management. The users have access to their files and can delete the files. On deletion, there is an option to retrieve the files / folders. The path of the files is stored in the database with the boolean field: IsActive which states if the file / folder is deleted.

The tabular structure in the database looks like:

Name    IsDirectory IsActive     Path                              Parent
----------------------------------------------------------------------------------------------
1.txt     0           0          /Home/usr1/New Folder/1.txt       /Home/usr1/New Folder/
a         1           0          /Home/usr1/New Folder/Docs        /Home/usr1/New Folder/
2.txt     0           0          /Home/usr1/New Folder/Docs/2.txt  /Home/usr1/New Folder/Docs

As you can see the that the user deletes a file (1.txt) from the "New Folder" and deletes the folder "Docs" which contains "2.txt". I am displaying this so that the user would restore or permanently delete the file or folder. I am capturing the parent folder of the folder/file too. So logically when I am displaying the data, the 2.txt file should not be visible but its parent i.e the Docs folder should be seen.

How can I write the query to get proper data. My current query helps in selecting the distinct Parent whose Parent is not in Path and then joining this to fetch the records.

WITH parents AS
    (SELECT DISTINCT par.Parent
        FROM   tblFilesNFolders par
        WHERE  par.Parent NOT IN (SELECT DISTINCT Path FROM tblFilesNFolders WHERE IsActive = 0) 
            AND par.IsActive = 0)
SELECT grid.Name, grid.IsDirectory, grid.IsActive, grid.Path, grid.Parent 
    FROM tblFilesNFolders grid
    INNER JOIN parents ON grid.Parent = parents.Parent
    WHERE grid.IsActive = 0 AND grid.Path LIKE '/Home/usr1%'

I was able to fetch the initial level, but when I change the path as /Home/usr1/New Folder/Docs% it does not show the 2.txt file.

What should my query be?

Upvotes: 0

Views: 112

Answers (1)

Twini
Twini

Reputation: 195

The problem in your query is that you need to perform another join. First select the data that you need to display (The entire Deleted Files without the hierarchies). Then Select those Parents which are not in Path. As the folder name is also in the path, then join this table on the first table on Parents to select the data.

SELECT [ID], [Path], [Name], [Parent], [IsDirectory], [IsActive], [Size], [Owner]
INTO #temp
FROM [MFTFileExplorer].[dbo].[tblFilesNFolders]
WHERE Parent LIKE '/home/usr1%’ AND IsActive = 0;
WITH finally AS
(SELECT DISTINCT Parent
FROM #temp m1
WHERE Parent NOT IN (
SELECT Path FROM #temp WHERE Path LIKE '/%'
)
)
SELECT * FROM #temp fin
INNER JOIN finally ON finally.Parent = fin.Parent
WHERE fin.Path LIKE '/%'

Remember to put ‘/’ before ‘%’ as it separates the folders from the files without the IsDirectory Column.

Upvotes: 1

Related Questions