migrations/2020-08-06-205355_update_community_post_count/down.sql

   1 -- Drop first
   2 DROP VIEW community_view;
   3
   4 DROP VIEW community_aggregates_view;
   5
   6 DROP VIEW community_fast_view;
   7
   8 DROP TABLE community_aggregates_fast;
   9
  10 CREATE VIEW community_aggregates_view AS
  11 SELECT
  12     c.id,
  13     c.name,
  14     c.title,
  15     c.icon,
  16     c.banner,
  17     c.description,
  18     c.category_id,
  19     c.creator_id,
  20     c.removed,
  21     c.published,
  22     c.updated,
  23     c.deleted,
  24     c.nsfw,
  25     c.actor_id,
  26     c.local,
  27     c.last_refreshed_at,
  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
  38 FROM
  39     community c
  40     LEFT JOIN user_ u ON c.creator_id = u.id
  41     LEFT JOIN category cat ON c.category_id = cat.id
  42     LEFT JOIN (
  43         SELECT
  44             p.community_id,
  45             count(DISTINCT p.id) AS posts,
  46             count(DISTINCT ct.id) AS comments
  47         FROM
  48             post p
  49             JOIN comment ct ON p.id = ct.post_id
  50         GROUP BY
  51             p.community_id) cd ON cd.community_id = c.id
  52     LEFT JOIN (
  53         SELECT
  54             community_id,
  55             count(*) AS subs
  56         FROM
  57             community_follower
  58         GROUP BY
  59             community_id) cf ON cf.community_id = c.id;
  60
  61 CREATE VIEW community_view AS
  62 SELECT
  63     cv.*,
  64     us.user AS user_id,
  65     us.is_subbed::bool AS subscribed
  66 FROM
  67     community_aggregates_view cv
  68     CROSS JOIN LATERAL (
  69         SELECT
  70             u.id AS user,
  71             coalesce(cf.community_id, 0) AS is_subbed
  72         FROM
  73             user_ u
  74             LEFT JOIN community_follower cf ON u.id = cf.user_id
  75                 AND cf.community_id = cv.id) AS us
  76 UNION ALL
  77 SELECT
  78     cv.*,
  79     NULL AS user_id,
  80     NULL AS subscribed
  81 FROM
  82     community_aggregates_view cv;
  83
  84 -- The community fast table
  85 CREATE TABLE community_aggregates_fast AS
  86 SELECT
  87     *
  88 FROM
  89     community_aggregates_view;
  90
  91 ALTER TABLE community_aggregates_fast
  92     ADD PRIMARY KEY (id);
  93
  94 CREATE VIEW community_fast_view AS
  95 SELECT
  96     ac.*,
  97     u.id AS user_id,
  98     (
  99         SELECT
 100             cf.id::boolean
 101         FROM
 102             community_follower cf
 103         WHERE
 104             u.id = cf.user_id
 105             AND ac.id = cf.community_id) AS subscribed
 106 FROM
 107     user_ u
 108     CROSS JOIN (
 109         SELECT
 110             ca.*
 111         FROM
 112             community_aggregates_fast ca) ac
 113 UNION ALL
 114 SELECT
 115     caf.*,
 116     NULL AS user_id,
 117     NULL AS subscribed
 118 FROM
 119     community_aggregates_fast caf;
 120