server/migrations/2020-08-06-205355_update_community_post_count/up.sql

   1 -- Drop first
   2 drop view community_view;
   3 drop view community_aggregates_view;
   4 drop view community_fast_view;
   5 drop table community_aggregates_fast;
   6
   7 create view community_aggregates_view as
   8 select
   9     c.id,
  10     c.name,
  11     c.title,
  12     c.icon,
  13     c.banner,
  14     c.description,
  15     c.category_id,
  16     c.creator_id,
  17     c.removed,
  18     c.published,
  19     c.updated,
  20     c.deleted,
  21     c.nsfw,
  22     c.actor_id,
  23     c.local,
  24     c.last_refreshed_at,
  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
  35 from community c
  36 left join user_ u on c.creator_id = u.id
  37 left join category cat on c.category_id = cat.id
  38 left join (
  39     select
  40         p.community_id,
  41         count(distinct p.id) as posts,
  42         count(distinct ct.id) as comments
  43     from post p
  44     left join comment ct on p.id = ct.post_id
  45     group by p.community_id
  46 ) cd on cd.community_id = c.id
  47 left join (
  48     select
  49         community_id,
  50         count(*) as subs
  51     from community_follower
  52     group by community_id
  53 ) cf on cf.community_id = c.id;
  54
  55 create view community_view as
  56 select
  57     cv.*,
  58     us.user as user_id,
  59     us.is_subbed::bool as subscribed
  60 from community_aggregates_view cv
  61 cross join lateral (
  62         select
  63                 u.id as user,
  64                 coalesce(cf.community_id, 0) as is_subbed
  65         from user_ u
  66         left join community_follower cf on u.id = cf.user_id and cf.community_id = cv.id
  67 ) as us
  68
  69 union all
  70
  71 select
  72     cv.*,
  73     null as user_id,
  74     null as subscribed
  75 from community_aggregates_view cv;
  76
  77 -- The community fast table
  78
  79 create table community_aggregates_fast as select * from community_aggregates_view;
  80 alter table community_aggregates_fast add primary key (id);
  81
  82 create view community_fast_view as
  83 select
  84 ac.*,
  85 u.id as user_id,
  86 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
  87 from user_ u
  88 cross join (
  89   select
  90   ca.*
  91   from community_aggregates_fast ca
  92 ) ac
  93
  94 union all
  95
  96 select
  97 caf.*,
  98 null as user_id,
  99 null as subscribed
 100 from community_aggregates_fast caf;