last activity of forums

2 files changed, 15 insertions, 14 deletions

diff --git a/apioforum/forum.py b/apioforum/forum.py
index 90c4592..9284671 100644
--- a/apioforum/forum.py
+++ b/apioforum/forum.py
@@ -10,6 +10,7 @@ from .db import get_db
 from .mdrender import render
 
 from sqlite3 import OperationalError
+import datetime
 
 bp = Blueprint("forum", __name__, url_prefix="/")
 
@@ -56,23 +57,23 @@ def view_forum(forum_id):
             ORDER BY tags.id;
             """,(thread['id'],)).fetchall()
 
-    subforums = db.execute("""
-            SELECT * FROM forums WHERE parent = ? ORDER BY name ASC
+    subforums_rows = db.execute("""
+            SELECT max(threads.updated) as updated, forums.* FROM forums
+            JOIN threads ON threads.forum=forums.id 
+            WHERE parent = ?
+            GROUP BY forums.id
+            ORDER BY name ASC
             """,(forum_id,)).fetchall()
-    
-    forum_last_activity = {}
-    for subforum in subforums:
-        result = db.execute(
-                """SELECT updated FROM threads
-                WHERE forum = ?
-                ORDER BY updated DESC;
-                """,(subforum['id'],)).fetchone()
-        forum_last_activity[subforum['id']] = result[0] if result != None else None
+    subforums = []
+    for s in subforums_rows:
+        a={}
+        a.update(s)
+        a['updated'] = datetime.datetime.fromisoformat(a['updated'])
+        subforums.append(a)
 
     return render_template("view_forum.html",
             forum=forum,
             subforums=subforums,
-            forum_last_activity=forum_last_activity,
             threads=threads,
             thread_tags=thread_tags,
             )
diff --git a/apioforum/templates/view_forum.html b/apioforum/templates/view_forum.html
index cf55cfe..9dc4227 100644
--- a/apioforum/templates/view_forum.html
+++ b/apioforum/templates/view_forum.html
@@ -25,8 +25,8 @@
 				</div>
 			</div>
 			<div class="listing-caption">
-				{% if forum_last_activity[subforum.id] %}
-					last activity {{ts(forum_last_activity[subforum.id])}} ago
+				{% if subforum.updated %}
+					last activity {{ts(subforum.updated)}} ago
 				{% else %}
 					no threads
 				{% endif %}