src/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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

CREATE VIEW api.post AS
	SELECT
		p.id,
		p.user_id,
		p.content,
		p.created,
		p.modified,
		COALESCE(c.cc, 0)
			AS comment_count,
		COALESCE(l.lc, 0)
			AS like_count
	FROM
		xssbook.post p
	LEFT JOIN (
		SELECT
			COUNT(c.id) as cc,
			c.post_id
		FROM
			xssbook.comment c
		GROUP BY
			c.post_id
	) c
	ON
		p.id = c.post_id
	LEFT JOIN (
		SELECT
			COUNT(l.id) as lc,
			l.post_id
		FROM
			xssbook.like l
		GROUP BY
			l.post_id
	) l
	ON
		p.id = l.post_id
	LEFT JOIN
		xssbook.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 xssbook.post
	TO rest_anon, rest_user;