summaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
-rw-r--r--apioforum/db.py7
-rw-r--r--apioforum/forum.py19
-rw-r--r--apioforum/templates/view_forum.html12
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>