From 0efffb90e4540d7883cdddfeeb887e271c8f065f Mon Sep 17 00:00:00 2001
From: ubq323 <ubq323>
Date: Wed, 23 Jun 2021 17:11:14 +0000
Subject: many database things, not really related to subforums but it's
 probably better to do all these things at the same time

---
 apioforum/db.py                     |  7 +++++++
 apioforum/forum.py                  | 19 +++++++++----------
 apioforum/templates/view_forum.html | 12 ++++++------
 3 files changed, 22 insertions(+), 16 deletions(-)

diff --git a/apioforum/db.py b/apioforum/db.py
index 84f268d..7dd635e 100644
--- a/apioforum/db.py
+++ b/apioforum/db.py
@@ -110,6 +110,13 @@ ALTER TABLE threads_new RENAME TO threads;
 COMMIT;
 PRAGMA foreign_keys = on;
 """,
+"""
+CREATE VIEW most_recent_posts AS
+    SELECT max(id), * FROM posts GROUP BY thread;
+
+CREATE VIEW number_of_posts AS
+    SELECT thread, count(*) AS num_replies FROM posts GROUP BY thread;
+""",
     
 ]
 
diff --git a/apioforum/forum.py b/apioforum/forum.py
index e379350..90c4592 100644
--- a/apioforum/forum.py
+++ b/apioforum/forum.py
@@ -33,16 +33,20 @@ def view_forum(forum_id):
     db = get_db()
     forum = db.execute("SELECT * FROM forums WHERE id = ?",(forum_id,)).fetchone()
     threads = db.execute(
-        """SELECT threads.id, threads.title, threads.creator, threads.created,
-        threads.updated, count(posts.id) as num_replies, max(posts.id), posts.author as last_user
+        """SELECT
+            threads.id, threads.title, threads.creator, threads.created,
+            threads.updated, number_of_posts.num_replies,
+            most_recent_posts.created as mrp_created,
+            most_recent_posts.author as mrp_author,
+            most_recent_posts.id as mrp_id,
+            most_recent_posts.content as mrp_content
         FROM threads
-        INNER JOIN posts ON posts.thread = threads.id
+        INNER JOIN most_recent_posts ON most_recent_posts.thread = threads.id
+        INNER JOIN number_of_posts ON number_of_posts.thread = threads.id
         WHERE threads.forum = ?
-        GROUP BY threads.id
         ORDER BY threads.updated DESC;
         """,(forum_id,)).fetchall()
     thread_tags = {}
-    preview_post = {}
     #todo: somehow optimise this
     for thread in threads:
         thread_tags[thread['id']] = db.execute(
@@ -51,10 +55,6 @@ def view_forum(forum_id):
             WHERE thread_tags.thread = ?
             ORDER BY tags.id;
             """,(thread['id'],)).fetchall()
-        preview_post[thread['id']]  = db.execute(
-            """SELECT * FROM posts WHERE thread = ?
-            ORDER BY created DESC;
-            """,(thread['id'],)).fetchone()
 
     subforums = db.execute("""
             SELECT * FROM forums WHERE parent = ? ORDER BY name ASC
@@ -75,7 +75,6 @@ def view_forum(forum_id):
             forum_last_activity=forum_last_activity,
             threads=threads,
             thread_tags=thread_tags,
-            preview_post=preview_post
             )
 
 @bp.route("/<int:forum_id>/create_thread",methods=("GET","POST"))
diff --git a/apioforum/templates/view_forum.html b/apioforum/templates/view_forum.html
index 926980e..cf55cfe 100644
--- a/apioforum/templates/view_forum.html
+++ b/apioforum/templates/view_forum.html
@@ -63,19 +63,19 @@
 					{{ ts(thread.created) }}
 				</div>
 			</div>
-			{% if preview_post[thread.id] %}
+			{#{% if thread.mrp_id %}#}
 				<div class="listing-caption">
-					{{ disp_user(preview_post[thread.id].author) }}
+					{{ disp_user(thread.mrp_author) }}
 					<span class="thread-preview-ts">
-						{{ ts(preview_post[thread.id].created) }}
+						{{ ts(thread.mrp_created) }}
 					</span>
 					<span class="thread-preview-post">
-						<a href="{{post_url(preview_post[thread.id])}}">
-							{{ preview_post[thread.id].content[:500]|e }}
+						<a href="{{url_for('thread.view_thread',thread_id=thread.id)}}#post_{{thread.mrp_id}}">
+							{{ thread.mrp_content[:500]|e }}
 						</a>
 					</span>
 				</div>
-			{% endif %}
+			{#{% endif %}#}
 		</div>
 	{%endfor%}
 </div>
-- 
cgit v1.2.3