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

   1 -- user
   2 drop view user_view;
   3 create view user_view as
   4 select id,
   5 name,
   6 avatar,
   7 fedi_name,
   8 admin,
   9 banned,
  10 published,
  11 (select count(*) from post p where p.creator_id = u.id) as number_of_posts,
  12 (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,
  13 (select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
  14 (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
  15 from user_ u;