summaryrefslogtreecommitdiff
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
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
-rw-r--r--lib/VNWeb/User/Page.pm14
-rw-r--r--util/sql/func.sql11
2 files changed, 13 insertions, 12 deletions
diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm
index 5ba088e6..a1d86c58 100644
--- a/lib/VNWeb/User/Page.pm
+++ b/lib/VNWeb/User/Page.pm
@@ -112,12 +112,12 @@ sub _votestats_ {
my $recent = tuwf->dbAlli('
SELECT vn.id, vn.title, vn.original, uv.vote,', sql_totime('uv.vote_date'), 'AS date
- FROM ulist_vns uv',
+ FROM ulist_vns uv
+ JOIN vn ON vn.id = uv.vid
+ WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id},
$own ? () : (
- 'JOIN ulist_labels ul ON ul.uid = uv.uid AND ul.id =', \7, 'AND NOT ul.private'
+ 'AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)'
), '
- JOIN vn ON vn.id = uv.vid
- WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id}, '
ORDER BY uv.vote_date DESC LIMIT', \8
);
@@ -152,11 +152,11 @@ TUWF::get qr{/$RE{uid}}, sub {
$u->{votes} = tuwf->dbAlli('
SELECT (uv.vote::numeric/10)::int AS idx, COUNT(uv.vote) as votes, SUM(uv.vote) AS total
- FROM ulist_vns uv',
+ FROM ulist_vns uv
+ WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id},
$own ? () : (
- 'JOIN ulist_labels ul ON ul.uid = uv.uid AND ul.id =', \7, 'AND NOT ul.private'
+ 'AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)'
), '
- WHERE uv.vote IS NOT NULL AND uv.uid =', \$u->{id}, '
GROUP BY (uv.vote::numeric/10)::int
');
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;