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

   1 -- user
   2 drop view user_view;
   3 create view user_view as
   4 select id,
   5 name,
   6 avatar,
   7 email,
   8 fedi_name,
   9 admin,
  10 banned,
  11 published,
  12 (select count(*) from post p where p.creator_id = u.id) as number_of_posts,
  13 (select coalesce(sum(score), 0) from post p, post_like pl where u.id = p.creator_id and p.id = pl.post_id) as post_score,
  14 (select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
  15 (select coalesce(sum(score), 0) from comment c, comment_like cl where u.id = c.creator_id and c.id = cl.comment_id) as comment_score
  16 from user_ u;