diff options
| -rw-r--r-- | apioforum/db.py | 7 | ||||
| -rw-r--r-- | apioforum/forum.py | 19 | ||||
| -rw-r--r-- | 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> |