migrations/2020-01-01-200418_add_email_to_user_view/down.sql

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