I have the following structure:
CREATE TABLE stories
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
dir TEXT,
alias TEXT,
title TEXT
);
CREATE TABLE tags
(
story_id INTEGER,
name TEXT
);
Now I want to select all stories that have (at least) given N tags, and do not have another N tags.
Example: All stories with "fantasy" and "nature", but without "dragons"
Here's a query I tried (for now just the "whitelist" part, but it's extremely slow - so I'm sure I'm doing it wrong.
SELECT s.*
FROM stories s
WHERE
(SELECT COUNT(*)
FROM tags t
WHERE
t.story_id = s.id
AND t.name IN ('fantasy', 'nature')
) = 2
If I add "LIMIT 10" at the end, it works (but very slowly).
No idea how to include the blacklist criteria into the query, though.
Ideas?
I have about 20.000 stories and 75.000 tag entries.
Aucun commentaire:
Enregistrer un commentaire