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

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