diff options
author | Yorhel <git@yorhel.nl> | 2020-01-08 15:33:42 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2020-01-08 15:34:19 +0100 |
commit | a462070881b07a4db4898df69d2cb1e04ca9b23e (patch) | |
tree | 51a4467757b952b6488c2c1c079aac9de5ddae14 /util/sql/func.sql | |
parent | 8046ec0d1c290420a0820b79849500af37a5739c (diff) |
ulist: Display vote stats for public votes even when "Vote" label is private
Fixes the last paragraph of https://vndb.org/t13365.36
Diffstat (limited to 'util/sql/func.sql')
-rw-r--r-- | util/sql/func.sql | 11 |
1 files changed, 6 insertions, 5 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql index 5423ab9b..af510e51 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -140,13 +140,14 @@ $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION update_users_ulist_stats(integer) RETURNS void AS $$ BEGIN WITH cnt(uid, votes, vns, wish) AS ( - SELECT u.id - , COUNT(*) FILTER (WHERE ul.id = 7) -- Voted - , COUNT(DISTINCT uvl.vid) FILTER (WHERE ul.id NOT IN(5,6)) -- Labelled, but not wishlish/blacklist - , COUNT(DISTINCT uvl.vid) FILTER (WHERE ul.id = 5) -- Wishlist + SELECT u.id + , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND uv.vote IS NOT NULL) -- Voted + , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND ul.id NOT IN(5,6)) -- Labelled, but not wishlish/blacklist + , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND ul.id = 5) -- Wishlist FROM users u LEFT JOIN ulist_vns_labels uvl ON uvl.uid = u.id - LEFT JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = u.id AND NOT ul.private + LEFT JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = u.id + LEFT JOIN ulist_vns uv ON uv.uid = u.id AND uv.vid = uvl.vid WHERE $1 IS NULL OR u.id = $1 GROUP BY u.id ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish FROM cnt WHERE id = uid; |