summaryrefslogtreecommitdiff
path: root/util/sql/func.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-01-08 15:33:42 +0100
committerYorhel <git@yorhel.nl>2020-01-08 15:34:19 +0100
commita462070881b07a4db4898df69d2cb1e04ca9b23e (patch)
tree51a4467757b952b6488c2c1c079aac9de5ddae14 /util/sql/func.sql
parent8046ec0d1c290420a0820b79849500af37a5739c (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.sql11
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;