db/rest/post/api_post.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

CREATE VIEW api.post AS
	SELECT
		p.id,
		p.user_id,
		p.content,
		p.date,
		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
	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;