Adding user details / overview page.

- Fixes #19

3 files changed, 13 insertions, 3 deletions

diff --git a/server/migrations/2019-02-27-170003_create_community/up.sql b/server/migrations/2019-02-27-170003_create_community/up.sql
index f78486d5..46b4df52 100644
--- a/server/migrations/2019-02-27-170003_create_community/up.sql
+++ b/server/migrations/2019-02-27-170003_create_community/up.sql
@@ -31,8 +31,6 @@ insert into category (name) values
 ('Meta'),
 ('Other');
 
-
-
 create table community (
   id serial primary key,
   name varchar(20) not null unique,
@@ -58,4 +56,4 @@ create table community_follower (
   published timestamp not null default now()
 );
 
-insert into community (name, title, category_id, creator_id) values ('main', 'The default Community', 1, 1);
+insert into community (name, title, category_id, creator_id) values ('main', 'The Default Community', 1, 1);
diff --git a/server/migrations/2019-04-08-015947_create_user_view/down.sql b/server/migrations/2019-04-08-015947_create_user_view/down.sql
new file mode 100644
index 00000000..c94d94c4
--- /dev/null
+++ b/server/migrations/2019-04-08-015947_create_user_view/down.sql
@@ -0,0 +1 @@
+drop view user_view;
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;
+