2 DROP VIEW community_view;
4 DROP VIEW community_aggregates_view;
6 DROP VIEW community_fast_view;
8 DROP TABLE community_aggregates_fast;
10 CREATE VIEW community_aggregates_view AS
28 u.actor_id AS creator_actor_id,
29 u.local AS creator_local,
30 u.name AS creator_name,
31 u.preferred_username AS creator_preferred_username,
32 u.avatar AS creator_avatar,
33 cat.name AS category_name,
34 coalesce(cf.subs, 0) AS number_of_subscribers,
35 coalesce(cd.posts, 0) AS number_of_posts,
36 coalesce(cd.comments, 0) AS number_of_comments,
37 hot_rank (cf.subs, c.published) AS hot_rank
40 LEFT JOIN user_ u ON c.creator_id = u.id
41 LEFT JOIN category cat ON c.category_id = cat.id
45 count(DISTINCT p.id) AS posts,
46 count(DISTINCT ct.id) AS comments
49 LEFT JOIN comment ct ON p.id = ct.post_id
51 p.community_id) cd ON cd.community_id = c.id
59 community_id) cf ON cf.community_id = c.id;
61 CREATE VIEW community_view AS
65 us.is_subbed::bool AS subscribed
67 community_aggregates_view cv
71 coalesce(cf.community_id, 0) AS is_subbed
74 LEFT JOIN community_follower cf ON u.id = cf.user_id
75 AND cf.community_id = cv.id) AS us
82 community_aggregates_view cv;
84 -- The community fast table
85 CREATE TABLE community_aggregates_fast AS
89 community_aggregates_view;
91 ALTER TABLE community_aggregates_fast
94 CREATE VIEW community_fast_view AS
102 community_follower cf
105 AND ac.id = cf.community_id) AS subscribed
112 community_aggregates_fast ca) ac
119 community_aggregates_fast caf;