Make SELECT subquery COUNT the total subscribers of a subscriber-CodePudding

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:

CodePudding user response：

await this.queryInstance.query(
'SELECT all_users_subbed_to.*, COUNT(all_users_subbed_to.id) 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]   
 );

CodePudding user response：

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]   
 );