CODEWITHSUNDEEP
phpmysqlsql
Adam Ramadhan
Adam Ramadhan

Reputation: 22810

Get followers and following in one query using MySQL

Ok lets say I'm making a social networks like twitter. I have a table called social where it gets or where we put our social thing.

example me ( uid = 1 )
friend1 ( uid = 2 )
friend2 ( uid = 3 )

The table

SID AUID BUID
1   1    2
2   1    3
3   2    1

The information that we get here is

user id 1(me) is following 2
user id 1(me) is following 3
user id 2 is following 1(me)

And the question is: Can we do something like the two queries below in one single query ?

function CountFollowers($uid){
    $count = $this->fetch("SELECT COUNT(BUID) as followers
                                   FROM social WHERE BUID = :uid", 
    array( 'uid' => $uid));
    return $count;
}

and 

function CountFollowing($uid){
    $count = $this->fetch("SELECT COUNT(AUID) as following
                                   FROM social WHERE AUID = :uid", 
    array( 'uid' => $uid));
    return $arrayofdata;
}

Thanks for looking in

Upvotes: 5

Views: 1664

Answers (4)

Hna
Hna

Reputation: 1006

This gets you two columns with the count of following and followers

SELECT (SELECT COUNT(BUID) as count FROM social WHERE BUID = :uid) as Followers
  , (SELECT COUNT(AUID) as count FROM social WHERE AUID = :uid) as Following

Upvotes: 6

a1ex07
a1ex07

Reputation: 37354

Do you need UNION ? 

SELECT 'followers' as `type`, COUNT(BUID) as count FROM social WHERE BUID = :uid
UNION
SELECT 'following' as `type` COUNT(AUID) as count FROM social WHERE AUID = :uid

Or you can do it in a different way :

SELECT COUNT(CASE 
WHEN BUID =:uid THEN 1
END) as  Followers,
COUNT(CASE
WHEN AUID=:uid THEN 1
END) as Following
 FROM social WHERE BUID = :uid OR AUID = :uid

Upvotes: 2

Thomas
Thomas

Reputation: 64635

Select Sum( Case When AUID = $uid Then 1 Else 0 End ) As Following
    , Sum( Case When BUID = $uid Then 1 Else 0 End ) As Followers
From Table
Where AUID = $uid
    Or BUID = $uid

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

SELECT
  COUNT(DISTINCT NULLIF(AUID, :uid)) AS MyFollowers,
  COUNT(DISTINCT NULLIF(BUID, :uid)) AS MeFollowing
FROM atable
WHERE :uid IN (AUID, BUID)

If (AUID, BUID) pairs are unique, then DISTINCT is not needed:

SELECT
  COUNT(NULLIF(AUID, :uid)) AS MyFollowers,
  COUNT(NULLIF(BUID, :uid)) AS MeFollowing
FROM atable
WHERE :uid IN (AUID, BUID)

Upvotes: 2

Related Questions