migrations/2019-04-08-015947_create_user_view/up.sql

   1 CREATE VIEW user_view AS
   2 SELECT
   3     id,
   4     name,
   5     fedi_name,
   6     admin,
   7     banned,
   8     published,
   9     (
  10         SELECT
  11             count(*)
  12         FROM
  13             post p
  14         WHERE
  15             p.creator_id = u.id) AS number_of_posts,
  16     (
  17         SELECT
  18             coalesce(sum(score), 0)
  19         FROM
  20             post p,
  21             post_like pl
  22         WHERE
  23             u.id = p.creator_id
  24             AND p.id = pl.post_id) AS post_score,
  25     (
  26         SELECT
  27             count(*)
  28         FROM
  29             comment c
  30         WHERE
  31             c.creator_id = u.id) AS number_of_comments,
  32     (
  33         SELECT
  34             coalesce(sum(score), 0)
  35         FROM
  36             comment c,
  37             comment_like cl
  38         WHERE
  39             u.id = c.creator_id
  40             AND c.id = cl.comment_id) AS comment_score
  41 FROM
  42     user_ u;
  43