diff options
author | Yorhel <git@yorhel.nl> | 2022-10-22 16:04:33 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2022-10-22 16:04:35 +0200 |
commit | 138944ced5a32236afa479c418a33dc7ae8cf9b5 (patch) | |
tree | 9b7a820ee1fa9a4fad4bbcca717f95f66d012c44 /sql | |
parent | bc9b1e24b65cf0151a71ade5aa498db528d4b9a3 (diff) |
AdvSearch: Add option to exclude lie tags
https://vndb.org/t18886.24
The compact form encoding is a big ugly mess, I wouldn't be surprised if
there's bugs.
Diffstat (limited to 'sql')
-rw-r--r-- | sql/func.sql | 10 | ||||
-rw-r--r-- | sql/schema.sql | 3 |
2 files changed, 7 insertions, 6 deletions
diff --git a/sql/func.sql b/sql/func.sql index 26f477bd..adc8ec07 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -296,20 +296,20 @@ BEGIN DELETE FROM tags_vn_inherit WHERE vid = uvid; END IF; - INSERT INTO tags_vn_inherit (tag, vid, rating, spoiler) + INSERT INTO tags_vn_inherit (tag, vid, rating, spoiler, lie) -- Add parent tags to each row in tags_vn_direct - WITH RECURSIVE t_all(lvl, tag, vid, vote, spoiler) AS ( - SELECT 15, tag, vid, rating, spoiler + WITH RECURSIVE t_all(lvl, tag, vid, vote, spoiler, lie) AS ( + SELECT 15, tag, vid, rating, spoiler, lie FROM tags_vn_direct WHERE (uvid IS NULL OR vid = uvid) UNION ALL - SELECT ta.lvl-1, tp.parent, ta.vid, ta.vote, ta.spoiler + SELECT ta.lvl-1, tp.parent, ta.vid, ta.vote, ta.spoiler, ta.lie FROM t_all ta JOIN tags_parents tp ON tp.id = ta.tag WHERE ta.lvl > 0 ) -- Merge duplicates - SELECT tag, vid, AVG(vote), MIN(spoiler) + SELECT tag, vid, AVG(vote), MIN(spoiler), bool_and(lie) FROM t_all WHERE tag IN(SELECT id FROM tags WHERE searchable) GROUP BY tag, vid; diff --git a/sql/schema.sql b/sql/schema.sql index 3c16d031..de4311df 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -882,7 +882,8 @@ CREATE TABLE tags_vn_inherit ( tag vndbid NOT NULL, vid vndbid NOT NULL, rating real NOT NULL, - spoiler smallint NOT NULL + spoiler smallint NOT NULL, + lie boolean NOT NULL ); -- threads |