migrations/2020-01-02-172755_add_show_avatar_and_email_notifications_to_user/down.sql

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