diff options
author | Yorhel <git@yorhel.nl> | 2020-08-19 08:39:33 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2020-08-19 08:39:33 +0200 |
commit | 31367028391ec4ba9a8d2d9837651b5a522b3340 (patch) | |
tree | e4e84742aea6dbf9e8bf826f7cebdd747d108fe9 | |
parent | ae0749087980fa99dbe439937c4b3496ca40f697 (diff) |
reviews: Count & list review comments as posts on user page
-rw-r--r-- | lib/VNWeb/Discussions/UPosts.pm | 30 | ||||
-rw-r--r-- | lib/VNWeb/User/Page.pm | 1 | ||||
-rw-r--r-- | sql/tableattrs.sql | 1 |
3 files changed, 20 insertions, 12 deletions
diff --git a/lib/VNWeb/Discussions/UPosts.pm b/lib/VNWeb/Discussions/UPosts.pm index 771fb3a9..0a0165d0 100644 --- a/lib/VNWeb/Discussions/UPosts.pm +++ b/lib/VNWeb/Discussions/UPosts.pm @@ -18,8 +18,8 @@ sub listing_ { td_ class => 'tc4', 'Title'; }}; tr_ sub { - my $url = "/$_->{tid}.$_->{num}"; - td_ class => 'tc1', sub { a_ href => $url, $_->{tid} }; + my $url = "/$_->{id}.$_->{num}"; + td_ class => 'tc1', sub { a_ href => $url, $_->{id} }; td_ class => 'tc2', sub { a_ href => $url, '.'.$_->{num} }; td_ class => 'tc3', fmtdate $_->{date}; td_ class => 'tc4', sub { @@ -40,17 +40,23 @@ TUWF::get qr{/$RE{uid}/posts}, sub { my $page = tuwf->validate(get => p => { upage => 1 })->data; - my $from_and_where = sql - 'FROM threads_posts tp - JOIN threads t ON t.id = tp.tid - WHERE NOT t.private AND NOT t.hidden AND NOT tp.hidden AND tp.uid =', \$u->{id}; + my $sql = sql '( + SELECT tp.tid, tp.num, tp.msg, t.title, tp.date + FROM threads_posts tp + JOIN threads t ON t.id = tp.tid + WHERE NOT t.private AND NOT t.hidden AND NOT tp.hidden AND tp.uid =', \$u->{id}, ' + UNION ALL + SELECT rp.id, rp.num, rp.msg, v.title, rp.date + FROM reviews_posts rp + JOIN reviews r ON r.id = rp.id + JOIN vn v ON v.id = r.vid + WHERE NOT rp.hidden AND rp.uid =', \$u->{id}, ' + ) p(id,num,msg,title,date)'; - my $count = tuwf->dbVali('SELECT count(*)', $from_and_where); - my $list = $count && tuwf->dbPagei( - { results => 50, page => $page }, - 'SELECT tp.tid, tp.num, substring(tp.msg from 1 for 1000) as msg, t.title - , ', sql_totime('tp.date'), 'as date', - $from_and_where, 'ORDER BY tp.date DESC' + my $count = tuwf->dbVali('SELECT count(*) FROM', $sql); + my $list = $count && tuwf->dbPagei({ results => 50, page => $page }, + 'SELECT id, num, substring(msg from 1 for 1000) as msg, title, ', sql_totime('date'), 'as date + FROM ', $sql, 'ORDER BY date DESC' ); my $own = auth && $u->{id} == auth->uid; diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm index 0b1b1770..c1ff681d 100644 --- a/lib/VNWeb/User/Page.pm +++ b/lib/VNWeb/User/Page.pm @@ -85,6 +85,7 @@ sub _info_table_ { } if $u->{c_imgvotes}; tr_ sub { my $stats = tuwf->dbRowi('SELECT COUNT(*) AS posts, COUNT(*) FILTER (WHERE num = 1) AS threads FROM threads_posts WHERE uid =', \$u->{id}); + $stats->{posts} += tuwf->dbVali('SELECT COUNT(*) FROM reviews_posts WHERE uid =', \$u->{id}); td_ 'Forum stats'; td_ !$stats->{posts} ? '-' : sub { txt_ sprintf '%d post%s, %d new thread%s. ', diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql index 191e18fe..34ad8e40 100644 --- a/sql/tableattrs.sql +++ b/sql/tableattrs.sql @@ -126,6 +126,7 @@ CREATE INDEX reports_new ON reports (date) WHERE status = 'new CREATE INDEX reports_lastmod ON reports (lastmod); CREATE UNIQUE INDEX reviews_vid_uid ON reviews (vid,uid); CREATE INDEX reviews_uid ON reviews (uid); +CREATE UNIQUE INDEX reviews_posts_uid ON reviews_posts (uid); CREATE UNIQUE INDEX reviews_votes_id_uid ON reviews_votes (id,uid); CREATE INDEX staff_alias_id ON staff_alias (id); CREATE UNIQUE INDEX tags_vn_pkey ON tags_vn (tag,vid,uid); |