From 1f647374a8cdf3bc5c2d29ff8be277b027925c8c Mon Sep 17 00:00:00 2001
From: Freya Murphy <freya@freyacat.org>
Date: Sat, 30 Mar 2024 16:36:54 -0400
Subject: post comments, refactor post loading, hide load more btn

---
 db/rest/post/api_post.sql | 15 +++++++++++++--
 1 file changed, 13 insertions(+), 2 deletions(-)

(limited to 'db/rest/post/api_post.sql')

diff --git a/db/rest/post/api_post.sql b/db/rest/post/api_post.sql
index b49289c..375f292 100644
--- a/db/rest/post/api_post.sql
+++ b/db/rest/post/api_post.sql
@@ -3,10 +3,21 @@ CREATE VIEW api.post AS
 		p.id,
 		p.user_id,
 		p.content,
-		p.date
+		p.date,
+		COALESCE(c.cc, 0)
+			AS comment_count
 	FROM
 		admin.post p
-	ORDER BY id DESC;
+	LEFT JOIN (
+		SELECT
+			COUNT(c.id) as cc,
+			c.post_id
+		FROM
+			admin.comment c
+		GROUP BY
+			c.post_id
+	) c ON p.id = c.post_id
+	ORDER BY p.id DESC;
 
 GRANT SELECT ON TABLE api.post
 	TO rest_anon, rest_user;
-- 
cgit v1.2.3-freya

