Only list threads with posts in the last 50 days as new and unread

diff --git a/database/functions/unread_posts.sql b/database/functions/unread_posts.sql
index 9c0023a2969084a0d6cc2ec36655201f8e57ec33..12a69ae3e6a6dba2373317826fb5219a7b62793b 100644 (file)
--- 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 2da8987c6f2f16ad7bcd5e16dee86e169be1783e..1853e4fc014b2cb5449d05721d8c884a9f2aa834 100644 (file)
--- 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 bf7dc3ca8f11b76788ef52aa4cc8bbc2b4e72582..f06848de982c5e8da0391b5c27ec3bf5ae9733ab 100644 (file)
--- 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))))