2 drop view community_view;
3 drop view community_aggregates_view;
4 drop view community_fast_view;
5 drop table community_aggregates_fast;
7 create view community_aggregates_view as
25 u.actor_id as creator_actor_id,
26 u.local as creator_local,
27 u.name as creator_name,
28 u.preferred_username as creator_preferred_username,
29 u.avatar as creator_avatar,
30 cat.name as category_name,
31 coalesce(cf.subs, 0) as number_of_subscribers,
32 coalesce(cd.posts, 0) as number_of_posts,
33 coalesce(cd.comments, 0) as number_of_comments,
34 hot_rank(cf.subs, c.published) as hot_rank
36 left join user_ u on c.creator_id = u.id
37 left join category cat on c.category_id = cat.id
41 count(distinct p.id) as posts,
42 count(distinct ct.id) as comments
44 join comment ct on p.id = ct.post_id
45 group by p.community_id
46 ) cd on cd.community_id = c.id
51 from community_follower
53 ) cf on cf.community_id = c.id;
55 create view community_view as
59 us.is_subbed::bool as subscribed
60 from community_aggregates_view cv
64 coalesce(cf.community_id, 0) as is_subbed
66 left join community_follower cf on u.id = cf.user_id and cf.community_id = cv.id
75 from community_aggregates_view cv;
77 -- The community fast table
79 create table community_aggregates_fast as select * from community_aggregates_view;
80 alter table community_aggregates_fast add primary key (id);
82 create view community_fast_view as
86 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
91 from community_aggregates_fast ca
100 from community_aggregates_fast caf;