keii.dev
1-- name: CreatePostWithBody :exec
2INSERT INTO
3 post (title, body, user_id, original_creator)
4VALUES
5 (?, ?, ?, ?);
6
7-- name: CreatePostWithHref :exec
8INSERT INTO
9 post (title, href, user_id, original_creator)
10VALUES
11 (?, ?, ?, ?);
12
13-- name: GetPostByID :one
14SELECT
15 post.id,
16 post.title,
17 post.href,
18 post.body,
19 user.username,
20 post.original_creator,
21 COUNT(DISTINCT user_like_post.id) AS like_count,
22 COUNT(DISTINCT post_comment.id) AS comment_count,
23 UNIX_TIMESTAMP (post.created_at) AS created_at
24FROM
25 post
26 LEFT JOIN user_like_post ON post.id = user_like_post.post_id
27 AND user_like_post.status = 'like'
28 LEFT JOIN post_comment ON post.id = post_comment.post_id
29 LEFT JOIN user ON post.user_id = user.id
30WHERE
31 post.id = ?
32GROUP BY
33 post.id,
34 user.username
35ORDER BY
36 post.created_at DESC
37LIMIT
38 25;
39
40-- name: GetPostByHref :one
41SELECT
42 post.title,
43 post.href
44FROM
45 post
46WHERE
47 post.href = ?;
48
49-- name: GetPosts :many
50SELECT
51 post.id,
52 post.title,
53 post.href,
54 post.body,
55 user.username,
56 post.original_creator,
57 COUNT(DISTINCT user_like_post.id) AS like_count,
58 COUNT(DISTINCT post_comment.id) AS comment_count,
59 UNIX_TIMESTAMP (post.created_at) AS created_at
60FROM
61 post
62 LEFT JOIN user_like_post ON post.id = user_like_post.post_id
63 AND user_like_post.status = 'like'
64 LEFT JOIN post_comment ON post.id = post_comment.post_id
65 LEFT JOIN user ON post.user_id = user.id
66GROUP BY
67 post.id,
68 user.username
69ORDER BY
70 post.created_at DESC;
71
72-- name: GetPostsUnlimited :many
73SELECT
74 post.id,
75 post.title,
76 post.href,
77 post.body,
78 user.username,
79 post.original_creator,
80 COUNT(DISTINCT user_like_post.id) AS like_count,
81 COUNT(DISTINCT post_comment.id) AS comment_count,
82 UNIX_TIMESTAMP (post.created_at) AS created_at
83FROM
84 post
85 LEFT JOIN user_like_post ON post.id = user_like_post.post_id
86 AND user_like_post.status = 'like'
87 LEFT JOIN post_comment ON post.id = post_comment.post_id
88 LEFT JOIN user ON post.user_id = user.id
89GROUP BY
90 post.id,
91 user.username
92ORDER BY
93 post.created_at DESC
94LIMIT
95 25;
96
97-- name: GetLatestPostByUserID :one
98SELECT
99 post.id,
100 post.title,
101 post.href,
102 post.body,
103 user.username,
104 post.original_creator,
105 COUNT(DISTINCT user_like_post.id) AS like_count,
106 COUNT(DISTINCT post_comment.id) AS comment_count,
107 UNIX_TIMESTAMP (post.created_at) AS created_at
108FROM
109 post
110 LEFT JOIN user_like_post ON post.id = user_like_post.post_id
111 AND user_like_post.status = 'like'
112 LEFT JOIN post_comment ON post.id = post_comment.post_id
113 LEFT JOIN user ON post.user_id = user.id
114WHERE
115 post.user_id = ?
116GROUP BY
117 post.id,
118 user.username
119ORDER BY
120 post.created_at DESC
121LIMIT
122 1;