migrations/2019-03-30-212058_create_post_view/up.sql

   1 -- Rank = ScaleFactor * sign(Score) * log(1 + abs(Score)) / (Time + 2)^Gravity
   2 CREATE OR REPLACE FUNCTION hot_rank (score numeric, published timestamp without time zone)
   3     RETURNS integer
   4     AS $$
   5 BEGIN
   6     -- hours_diff:=EXTRACT(EPOCH FROM (timezone('utc',now()) - published))/3600
   7     RETURN floor(10000 * log(greatest (1, score + 3)) / power(((EXTRACT(EPOCH FROM (timezone('utc', now()) - published)) / 3600) + 2), 1.8))::integer;
   8 END;
   9 $$
  10 LANGUAGE plpgsql;
  11
  12 CREATE VIEW post_view AS
  13 with all_post AS (
  14     SELECT
  15         p.*,
  16         (
  17             SELECT
  18                 name
  19             FROM
  20                 user_
  21             WHERE
  22                 p.creator_id = user_.id) AS creator_name,
  23         (
  24             SELECT
  25                 name
  26             FROM
  27                 community
  28             WHERE
  29                 p.community_id = community.id) AS community_name,
  30         (
  31             SELECT
  32                 removed
  33             FROM
  34                 community c
  35             WHERE
  36                 p.community_id = c.id) AS community_removed,
  37         (
  38             SELECT
  39                 count(*)
  40             FROM
  41                 comment
  42             WHERE
  43                 comment.post_id = p.id) AS number_of_comments,
  44         coalesce(sum(pl.score), 0) AS score,
  45         count(
  46             CASE WHEN pl.score = 1 THEN
  47                 1
  48             ELSE
  49                 NULL
  50             END) AS upvotes,
  51         count(
  52             CASE WHEN pl.score = - 1 THEN
  53                 1
  54             ELSE
  55                 NULL
  56             END) AS downvotes,
  57         hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
  58     FROM
  59         post p
  60         LEFT JOIN post_like pl ON p.id = pl.post_id
  61     GROUP BY
  62         p.id
  63 )
  64 SELECT
  65     ap.*,
  66     u.id AS user_id,
  67     coalesce(pl.score, 0) AS my_vote,
  68     (
  69         SELECT
  70             cf.id::bool
  71         FROM
  72             community_follower cf
  73         WHERE
  74             u.id = cf.user_id
  75             AND cf.community_id = ap.community_id) AS subscribed,
  76     (
  77         SELECT
  78             pr.id::bool
  79         FROM
  80             post_read pr
  81         WHERE
  82             u.id = pr.user_id
  83             AND pr.post_id = ap.id) AS read,
  84     (
  85         SELECT
  86             ps.id::bool
  87         FROM
  88             post_saved ps
  89         WHERE
  90             u.id = ps.user_id
  91             AND ps.post_id = ap.id) AS saved
  92 FROM
  93     user_ u
  94     CROSS JOIN all_post ap
  95     LEFT JOIN post_like pl ON u.id = pl.user_id
  96         AND ap.id = pl.post_id
  97     UNION ALL
  98     SELECT
  99         ap.*,
 100         NULL AS user_id,
 101         NULL AS my_vote,
 102         NULL AS subscribed,
 103         NULL AS read,
 104         NULL AS saved
 105     FROM
 106         all_post ap;
 107