I trying to create a query that counts the total subscribers of a subscriber. It currently looks like this:
await this.queryInstance.query( 'SELECT all_users_subbed_to.* , (SELECT COUNT(??????)) AS subscribers_sub_count FROM (SELECT publisher_id, subscriber_id, u2.username AS username, u2.user_photo AS user_photo FROM subscribers s INNER JOIN users u ON (u.id = s.subscriber_id) INNER JOIN users u2 ON (u2.id = s.publisher_id) WHERE subscriber_id = ($1) LIMIT 20 OFFSET ($2)) AS all_users_subbed_to;' , [currentUserId = 80, offset] );
The FROM CLAUSE AKA all_users_subbed_to
is WORKING correctly and displays a ALL the subscribers the current user has. The data comes back as this:
"subscribedToCurrentUser": [ { "publisher_id": 84, "subscriber_id": 80, "username": "supercoookie", "user_photo": "profile-pic-for-supercoookie.jpeg" }, { "publisher_id": 88, "subscriber_id": 80, "username": "GERPAL1", "user_photo": "profile-pic-for-GERPAL1.jpeg" } ]
The issue I am having is getting the total subscriber counts for the list of those subscribers. I need to use the subscribers publisher_id ie all_users_subbed_to.publisher_id
and get their total subs (using COUNT) from the subscribers table. I would like to create a new column called have subscribers_sub_count
that contains that total.
Any ideas?
It should look like this:
"subscribedToCurrentUser": [ { "publisher_id": 84, "subscriber_id": 80, "username": "supercoookie", "user_photo": "profile-pic-for-supercoookie.jpeg", "subscribers_sub_count": 3 }, { "publisher_id": 88, "subscriber_id": 80, "username": "GERPAL1", "user_photo": "profile-pic-for-GERPAL1.jpeg", "subscribers_sub_count": 70 } ]
The subscribers table looks like this:
Advertisement
Answer
Fixed it. It just needed a WHERE clause that used data from all_users_subbed_to
await this.queryInstance.query( 'SELECT all_users_subbed_to.* , (SELECT COUNT(*) FROM subscribers s2 WHERE s2.publisher_id = all_users_subbed_to.publisher_id) AS subscribers_sub_count AS subscribers_sub_count FROM (SELECT publisher_id, subscriber_id, u2.username AS username, u2.user_photo AS user_photo FROM subscribers s INNER JOIN users u ON (u.id = s.subscriber_id) INNER JOIN users u2 ON (u2.id = s.publisher_id) WHERE subscriber_id = ($1) LIMIT 20 OFFSET ($2)) AS all_users_subbed_to;' , [currentUserId = 80, offset] );