From aa93c4baf476e9fa421e1a5ce0d97e9527d33417 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sat, 20 Jun 2020 14:52:43 +0200 Subject: [PATCH] Only list threads with posts in the last 50 days as new and unread --- database/functions/unread_posts.sql | 3 ++- database/ndawn.sql | 2 ++ lib/NDWeb/Controller/Forum.pm | 3 ++- 3 files changed, 6 insertions(+), 2 deletions(-) diff --git a/database/functions/unread_posts.sql b/database/functions/unread_posts.sql index 9c0023a..12a69ae 100644 --- a/database/functions/unread_posts.sql +++ b/database/functions/unread_posts.sql @@ -7,7 +7,8 @@ FROM( FROM forum_threads ft LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv USING (ftid) - WHERE COALESCE(ft.mtime > ftv.time,TRUE) + WHERE ft.mtime > NOW() - '50 days'::interval + AND COALESCE(ft.mtime > ftv.time,TRUE) AND ((fbid > 0 AND fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))) ) OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1) diff --git a/database/ndawn.sql b/database/ndawn.sql index 2da8987..1853e4f 100644 --- a/database/ndawn.sql +++ b/database/ndawn.sql @@ -3316,6 +3316,8 @@ CREATE INDEX forum_posts_ftid_index ON forum_posts USING btree (ftid); CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin (textsearch); +CREATE INDEX forum_threads_mtime_index ON forum_threads USING btree (mtime); + -- -- Name: groupmembers_uid_key; Type: INDEX; Schema: public; Owner: ndawn -- diff --git a/lib/NDWeb/Controller/Forum.pm b/lib/NDWeb/Controller/Forum.pm index bf7dc3c..f06848d 100644 --- a/lib/NDWeb/Controller/Forum.pm +++ b/lib/NDWeb/Controller/Forum.pm @@ -73,7 +73,8 @@ FROM forum_categories fc JOIN users u ON u.uid = ft.uid LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid -WHERE COALESCE(ft.mtime > ftv.time,TRUE) +WHERE ft.mtime > NOW() - '50 days'::interval + AND COALESCE(ft.mtime > ftv.time,TRUE) AND ft.ftid IN (SELECT ftid FROM forum_posts WHERE ftid = ft.ftid) AND ((fbid > 0 AND fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1)))) -- 2.39.2