migrations/2019-04-03-155309_create_comment_view/up.sql

   1 CREATE VIEW comment_view AS
   2 with all_comment AS (
   3     SELECT
   4         c.*,
   5         (
   6             SELECT
   7                 community_id
   8             FROM
   9                 post p
  10             WHERE
  11                 p.id = c.post_id),
  12             (
  13                 SELECT
  14                     u.banned
  15                 FROM
  16                     user_ u
  17                 WHERE
  18                     c.creator_id = u.id) AS banned,
  19                 (
  20                     SELECT
  21                         cb.id::bool
  22                     FROM
  23                         community_user_ban cb,
  24                         post p
  25                     WHERE
  26                         c.creator_id = cb.user_id
  27                         AND p.id = c.post_id
  28                         AND p.community_id = cb.community_id) AS banned_from_community,
  29                     (
  30                         SELECT
  31                             name
  32                         FROM
  33                             user_
  34                         WHERE
  35                             c.creator_id = user_.id) AS creator_name,
  36                         coalesce(sum(cl.score), 0) AS score,
  37                     count(
  38                         CASE WHEN cl.score = 1 THEN
  39                             1
  40                         ELSE
  41                             NULL
  42                         END) AS upvotes,
  43                     count(
  44                         CASE WHEN cl.score = - 1 THEN
  45                             1
  46                         ELSE
  47                             NULL
  48                         END) AS downvotes
  49                 FROM
  50                     comment c
  51                 LEFT JOIN comment_like cl ON c.id = cl.comment_id
  52             GROUP BY
  53                 c.id
  54 )
  55     SELECT
  56         ac.*,
  57         u.id AS user_id,
  58         coalesce(cl.score, 0) AS my_vote,
  59     (
  60         SELECT
  61             cs.id::bool
  62         FROM
  63             comment_saved cs
  64         WHERE
  65             u.id = cs.user_id
  66             AND cs.comment_id = ac.id) AS saved
  67 FROM
  68     user_ u
  69     CROSS JOIN all_comment ac
  70     LEFT JOIN comment_like cl ON u.id = cl.user_id
  71         AND ac.id = cl.comment_id
  72     UNION ALL
  73     SELECT
  74         ac.*,
  75         NULL AS user_id,
  76         NULL AS my_vote,
  77         NULL AS saved
  78     FROM
  79         all_comment ac;
  80
  81 CREATE VIEW reply_view AS
  82 with closereply AS (
  83     SELECT
  84         c2.id,
  85         c2.creator_id AS sender_id,
  86         c.creator_id AS recipient_id
  87     FROM
  88         comment c
  89         INNER JOIN comment c2 ON c.id = c2.parent_id
  90     WHERE
  91         c2.creator_id != c.creator_id
  92         -- Do union where post is null
  93     UNION
  94     SELECT
  95         c.id,
  96         c.creator_id AS sender_id,
  97         p.creator_id AS recipient_id
  98     FROM
  99         comment c,
 100         post p
 101     WHERE
 102         c.post_id = p.id
 103         AND c.parent_id IS NULL
 104         AND c.creator_id != p.creator_id
 105 )
 106 SELECT
 107     cv.*,
 108     closereply.recipient_id
 109 FROM
 110     comment_view cv,
 111     closereply
 112 WHERE
 113     closereply.id = cv.id;
 114