How to exclude results in a query using a subquery as blacklist

I’m trying to query a database for text, use a (sub)query as a “blacklist”. The twist: if any of the results from the main query have an identical beginning as any of the results from the subquery, they should be skipped.

A bit of background: I’m building a filebrowser, and using a table of foldernames to maintain the “favorited” state of entries. These favorites are intended to work in a cascading manner, i.e. any paths below a “explicitly” favorited path will be “implicitly” favorited.

Consider the following data:

foldername	is_favorite	is_implicit_favorite
foo/	1	0
foo/bar/	1	1
foo/bar/baz/	0	1
foo/bar2/	0	1
foo/bar2/baz/	0	1
foo2/bar/	0	0
foo2/bar/baz/	0	0

Adding a favorite is easy: all folders below the given path will have their “implicit” state set. But I’ve found it hard to come up with a simple approach for the opposite – “unfavoriting” a folder. Because here, I want to the query to skip folders that should remain implicitly favorited (in the example above, unfavoriting “foo/” should skip folders below “foo/bar”).

I’ve tried various solutions – the following approach comes close, but unfortunately only works with a single favorited subfolder:

SELECT DISTINCT folders.foldername FROM folders 
JOIN (
    SELECT folders.foldername FROM folders 
    WHERE folders.foldername LIKE 'foo/' || '%'
    AND folders.is_favorite = 1
) favs ON folders.foldername NOT LIKE favs.foldername || '%'
WHERE folders.foldername LIKE 'foo/' || '%'

It does what I want – unfavoriting “foo” should result in “foo/bar2/” and “foo/bar2/baz/” no longer being implicitly favorited, while “foo/bar/” (and its subfolders) remain unchanged:

folders.foldername
foo/
foo/bar2/
foo/bar2/baz/

View it online: http://sqlfiddle.com/#!5/8a04e/14/0

EDIT: thanks to Christians answer pointing me to the EXCEPT operator, I was able to come up with the following modified version that also works for multiple favorites.

SELECT folders.foldername FROM folders 
WHERE folders.foldername LIKE 'foo/' || '%'
EXCEPT 
SELECT folders.foldername FROM folders 
INNER JOIN (
    SELECT folders.foldername FROM folders 
    WHERE folders.foldername <> 'foo/'
    AND folders.is_favorite = 1
) favs 
ON folders.foldername LIKE favs.foldername || '%'

I’m very happy that it’s even possible at all to express this in pure SQL – I was briefly tempted to make it a hybrid SQL/node.js solution.

And of course, I’d happily accept any advice on possible optimizations!

Thanks.

Answer

This might not be the most efficient query, but it does the trick:

SELECT folders.foldername FROM folders 
WHERE folders.foldername LIKE 'foo/' || '%'
EXCEPT 
SELECT folders.foldername FROM folders 
INNER JOIN (
    SELECT folders.foldername FROM folders 
    WHERE folders.foldername <> 'foo/'
    AND folders.is_favorite = 1
) favs 
ON folders.foldername LIKE favs.foldername || '%'

(Updated my original post)

Advertisement

Answer