diff options
author | Dessalines <tyhou13@gmx.com> | 2019-04-07 22:19:02 -0700 |
---|---|---|
committer | Dessalines <tyhou13@gmx.com> | 2019-04-07 22:19:02 -0700 |
commit | 49bf16e7d451388d894f93a994f3bf18571f9594 (patch) | |
tree | 1802bf775a0dd97503670c38ceb13d9aa78d95b6 /server/migrations/2019-04-08-015947_create_user_view/up.sql | |
parent | a61516439406b7884e19d9ae8a1875c728bbe628 (diff) |
Adding user details / overview page.
- Fixes #19
Diffstat (limited to 'server/migrations/2019-04-08-015947_create_user_view/up.sql')
-rw-r--r-- | server/migrations/2019-04-08-015947_create_user_view/up.sql | 11 |
1 files changed, 11 insertions, 0 deletions
diff --git a/server/migrations/2019-04-08-015947_create_user_view/up.sql b/server/migrations/2019-04-08-015947_create_user_view/up.sql new file mode 100644 index 00000000..69d052de --- /dev/null +++ b/server/migrations/2019-04-08-015947_create_user_view/up.sql @@ -0,0 +1,11 @@ +create view user_view as +select id, +name, +fedi_name, +published, +(select count(*) from post p where p.creator_id = u.id) as number_of_posts, +(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, +(select count(*) from comment c where c.creator_id = u.id) as number_of_comments, +(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 +from user_ u; + |