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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
create view user_mention_mview as
with all_comment as
(
select
ca.*
from comment_aggregates_mview ca
)
select
ac.id,
um.id as user_mention_id,
ac.creator_id,
ac.post_id,
ac.parent_id,
ac.content,
ac.removed,
um.read,
ac.published,
ac.updated,
ac.deleted,
ac.community_id,
ac.banned,
ac.banned_from_community,
ac.creator_name,
ac.creator_avatar,
ac.score,
ac.upvotes,
ac.downvotes,
u.id as user_id,
coalesce(cl.score, 0) as my_vote,
(select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
um.recipient_id
from user_ u
cross join all_comment ac
left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
left join user_mention um on um.comment_id = ac.id
union all
select
ac.id,
um.id as user_mention_id,
ac.creator_id,
ac.post_id,
ac.parent_id,
ac.content,
ac.removed,
um.read,
ac.published,
ac.updated,
ac.deleted,
ac.community_id,
ac.banned,
ac.banned_from_community,
ac.creator_name,
ac.creator_avatar,
ac.score,
ac.upvotes,
ac.downvotes,
null as user_id,
null as my_vote,
null as saved,
um.recipient_id
from all_comment ac
left join user_mention um on um.comment_id = ac.id
;
|
