login and register, liking on homepage

1 files changed, 37 insertions, 0 deletions

diff --git a/src/db/rest/post/api_post.sql b/src/db/rest/post/api_post.sql
new file mode 100644
index 0000000..0d60473
--- /dev/null
+++ b/src/db/rest/post/api_post.sql
@@ -0,0 +1,37 @@
+CREATE VIEW api.post AS
+	SELECT
+		p.id,
+		p.user_id,
+		p.content,
+		p.created,
+		p.modified,
+		COALESCE(c.cc, 0)
+			AS comment_count
+	FROM
+		admin.post p
+	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
+	LEFT JOIN
+		admin.user u
+	ON
+		u.id = p.user_id
+	WHERE
+		p.deleted <> TRUE
+	AND
+		u.deleted <> TRUE
+	ORDER BY
+		p.id DESC;
+
+GRANT SELECT ON TABLE api.post
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.post
+	TO rest_anon, rest_user;