diff options
Diffstat (limited to 'src/db/rest/user/api_user.sql')
| -rw-r--r-- | src/db/rest/user/api_user.sql | 67 |
1 files changed, 66 insertions, 1 deletions
diff --git a/src/db/rest/user/api_user.sql b/src/db/rest/user/api_user.sql index 6735775..d71fd1b 100644 --- a/src/db/rest/user/api_user.sql +++ b/src/db/rest/user/api_user.sql @@ -13,9 +13,74 @@ CREATE VIEW api.user AS u.profile_bio, u.created, u.modified, - u.seen + u.seen, + COALESCE(f.fc, 0) + AS follower_count, + COALESCE(fl.fc, 0) + AS followed_count, + COALESCE(c.cc, 0) + AS comment_count, + COALESCE(p.pc, 0) + AS post_count, + COALESCE(l.lc, 0) + AS like_count FROM admin.user u + LEFT JOIN ( + SELECT + COUNT(f.id) as fc, + f.followee_id + FROM + admin.follow f + GROUP BY + f.followee_id + ) f + ON + u.id = f.followee_id + LEFT JOIN ( + SELECT + COUNT(fl.id) as fc, + fl.follower_id + FROM + admin.follow fl + GROUP BY + fl.follower_id + ) fl + ON + u.id = fl.follower_id + LEFT JOIN ( + SELECT + COUNT(c.id) as cc, + c.user_id + FROM + admin.comment c + GROUP BY + c.user_id + ) c + ON + u.id = c.user_id + LEFT JOIN ( + SELECT + COUNT(p.id) as pc, + p.user_id + FROM + admin.post p + GROUP BY + p.user_id + ) p + ON + u.id = p.user_id + LEFT JOIN ( + SELECT + COUNT(l.id) as lc, + l.user_id + FROM + admin.like l + GROUP BY + l.user_id + ) l + ON + u.id = l.user_id WHERE u.deleted <> TRUE; |