diff options
author | Dessalines <happydooby@gmail.com> | 2019-05-01 22:26:31 -0700 |
---|---|---|
committer | Dessalines <happydooby@gmail.com> | 2019-05-01 22:26:31 -0700 |
commit | b304b482eafd8ab87bd9fb8c263773a038ea1e6f (patch) | |
tree | 4aaee50876fd32c1f01fd4c5b05c57cd6aa096be /server/migrations | |
parent | b2d8bf304eff145999ed38956e88978a6e1b44fb (diff) |
Reworking some UI. Adding proper trending communities with hot rank.
- Breaking out subscribed and all into radios. Fixes #142
Diffstat (limited to 'server/migrations')
-rw-r--r-- | server/migrations/2019-05-02-051656_community_view_hot_rank/down.sql | 28 | ||||
-rw-r--r-- | server/migrations/2019-05-02-051656_community_view_hot_rank/up.sql | 29 |
2 files changed, 57 insertions, 0 deletions
diff --git a/server/migrations/2019-05-02-051656_community_view_hot_rank/down.sql b/server/migrations/2019-05-02-051656_community_view_hot_rank/down.sql new file mode 100644 index 00000000..0f3a58a8 --- /dev/null +++ b/server/migrations/2019-05-02-051656_community_view_hot_rank/down.sql @@ -0,0 +1,28 @@ +drop view community_view; +create view community_view as +with all_community as +( + select *, + (select name from user_ u where c.creator_id = u.id) as creator_name, + (select name from category ct where c.category_id = ct.id) as category_name, + (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers, + (select count(*) from post p where p.community_id = c.id) as number_of_posts, + (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments + from community c +) + +select +ac.*, +u.id as user_id, +(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed +from user_ u +cross join all_community ac + +union all + +select +ac.*, +null as user_id, +null as subscribed +from all_community ac +; diff --git a/server/migrations/2019-05-02-051656_community_view_hot_rank/up.sql b/server/migrations/2019-05-02-051656_community_view_hot_rank/up.sql new file mode 100644 index 00000000..e7e75366 --- /dev/null +++ b/server/migrations/2019-05-02-051656_community_view_hot_rank/up.sql @@ -0,0 +1,29 @@ +drop view community_view; +create view community_view as +with all_community as +( + select *, + (select name from user_ u where c.creator_id = u.id) as creator_name, + (select name from category ct where c.category_id = ct.id) as category_name, + (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers, + (select count(*) from post p where p.community_id = c.id) as number_of_posts, + (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments, + hot_rank((select count(*) from community_follower cf where cf.community_id = c.id), c.published) as hot_rank + from community c +) + +select +ac.*, +u.id as user_id, +(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed +from user_ u +cross join all_community ac + +union all + +select +ac.*, +null as user_id, +null as subscribed +from all_community ac +; |