migrations/2019-05-02-051656_community_view_hot_rank/down.sql

   1 DROP VIEW community_view;
   2
   3 CREATE VIEW community_view AS
   4 with all_community AS (
   5     SELECT
   6         *,
   7         (
   8             SELECT
   9                 name
  10             FROM
  11                 user_ u
  12             WHERE
  13                 c.creator_id = u.id) AS creator_name,
  14         (
  15             SELECT
  16                 name
  17             FROM
  18                 category ct
  19             WHERE
  20                 c.category_id = ct.id) AS category_name,
  21         (
  22             SELECT
  23                 count(*)
  24             FROM
  25                 community_follower cf
  26             WHERE
  27                 cf.community_id = c.id) AS number_of_subscribers,
  28         (
  29             SELECT
  30                 count(*)
  31             FROM
  32                 post p
  33             WHERE
  34                 p.community_id = c.id) AS number_of_posts,
  35         (
  36             SELECT
  37                 count(*)
  38             FROM
  39                 comment co,
  40                 post p
  41             WHERE
  42                 c.id = p.community_id
  43                 AND p.id = co.post_id) AS number_of_comments
  44     FROM
  45         community c
  46 )
  47 SELECT
  48     ac.*,
  49     u.id AS user_id,
  50     (
  51         SELECT
  52             cf.id::boolean
  53         FROM
  54             community_follower cf
  55         WHERE
  56             u.id = cf.user_id
  57             AND ac.id = cf.community_id) AS subscribed
  58 FROM
  59     user_ u
  60     CROSS JOIN all_community ac
  61 UNION ALL
  62 SELECT
  63     ac.*,
  64     NULL AS user_id,
  65     NULL AS subscribed
  66 FROM
  67     all_community ac;
  68