migrations/2019-05-02-051656_community_view_hot_rank/up.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         hot_rank ((
  45             SELECT
  46                 count(*)
  47             FROM community_follower cf
  48             WHERE
  49                 cf.community_id = c.id), c.published) AS hot_rank
  50 FROM
  51     community c
  52 )
  53 SELECT
  54     ac.*,
  55     u.id AS user_id,
  56     (
  57         SELECT
  58             cf.id::boolean
  59         FROM
  60             community_follower cf
  61         WHERE
  62             u.id = cf.user_id
  63             AND ac.id = cf.community_id) AS subscribed
  64 FROM
  65     user_ u
  66     CROSS JOIN all_community ac
  67 UNION ALL
  68 SELECT
  69     ac.*,
  70     NULL AS user_id,
  71     NULL AS subscribed
  72 FROM
  73     all_community ac;
  74