migrations/2019-04-03-155205_create_community_view/up.sql

   1 CREATE VIEW community_view AS
   2 with all_community AS (
   3     SELECT
   4         *,
   5         (
   6             SELECT
   7                 name
   8             FROM
   9                 user_ u
  10             WHERE
  11                 c.creator_id = u.id) AS creator_name,
  12         (
  13             SELECT
  14                 name
  15             FROM
  16                 category ct
  17             WHERE
  18                 c.category_id = ct.id) AS category_name,
  19         (
  20             SELECT
  21                 count(*)
  22             FROM
  23                 community_follower cf
  24             WHERE
  25                 cf.community_id = c.id) AS number_of_subscribers,
  26         (
  27             SELECT
  28                 count(*)
  29             FROM
  30                 post p
  31             WHERE
  32                 p.community_id = c.id) AS number_of_posts,
  33         (
  34             SELECT
  35                 count(*)
  36             FROM
  37                 comment co,
  38                 post p
  39             WHERE
  40                 c.id = p.community_id
  41                 AND p.id = co.post_id) AS number_of_comments
  42     FROM
  43         community c
  44 )
  45 SELECT
  46     ac.*,
  47     u.id AS user_id,
  48     (
  49         SELECT
  50             cf.id::boolean
  51         FROM
  52             community_follower cf
  53         WHERE
  54             u.id = cf.user_id
  55             AND ac.id = cf.community_id) AS subscribed
  56 FROM
  57     user_ u
  58     CROSS JOIN all_community ac
  59 UNION ALL
  60 SELECT
  61     ac.*,
  62     NULL AS user_id,
  63     NULL AS subscribed
  64 FROM
  65     all_community ac;
  66
  67 CREATE VIEW community_moderator_view AS
  68 SELECT
  69     *,
  70     (
  71         SELECT
  72             name
  73         FROM
  74             user_ u
  75         WHERE
  76             cm.user_id = u.id) AS user_name,
  77     (
  78         SELECT
  79             name
  80         FROM
  81             community c
  82         WHERE
  83             cm.community_id = c.id) AS community_name
  84 FROM
  85     community_moderator cm;
  86
  87 CREATE VIEW community_follower_view AS
  88 SELECT
  89     *,
  90     (
  91         SELECT
  92             name
  93         FROM
  94             user_ u
  95         WHERE
  96             cf.user_id = u.id) AS user_name,
  97     (
  98         SELECT
  99             name
 100         FROM
 101             community c
 102         WHERE
 103             cf.community_id = c.id) AS community_name
 104 FROM
 105     community_follower cf;
 106
 107 CREATE VIEW community_user_ban_view AS
 108 SELECT
 109     *,
 110     (
 111         SELECT
 112             name
 113         FROM
 114             user_ u
 115         WHERE
 116             cm.user_id = u.id) AS user_name,
 117     (
 118         SELECT
 119             name
 120         FROM
 121             community c
 122         WHERE
 123             cm.community_id = c.id) AS community_name
 124 FROM
 125     community_user_ban cm;
 126
 127 CREATE VIEW site_view AS
 128 SELECT
 129     *,
 130     (
 131         SELECT
 132             name
 133         FROM
 134             user_ u
 135         WHERE
 136             s.creator_id = u.id) AS creator_name,
 137     (
 138         SELECT
 139             count(*)
 140         FROM
 141             user_) AS number_of_users,
 142     (
 143         SELECT
 144             count(*)
 145         FROM
 146             post) AS number_of_posts,
 147     (
 148         SELECT
 149             count(*)
 150         FROM
 151             comment) AS number_of_comments
 152 FROM
 153     site s;
 154