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

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