Geek Answers Handbook

Get followers and follow in a single query using MySQL

Well, let's say I create social networks like twitter. I have a table called social, where it gets to, or where we put our social thing.

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

Table

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

The information 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 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 watching

+5
source share
4 answers

This gives you two columns counting the next and the 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
+4
source

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 differently:

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
+2
source 
SELECT
  COUNT(DISTINCT NULLIF(AUID, :uid)) AS MyFollowers,
  COUNT(DISTINCT NULLIF(BUID, :uid)) AS MeFollowing
FROM atable
WHERE :uid IN (AUID, BUID)

(AUID, BUID) , DISTINCT :

SELECT
  COUNT(NULLIF(AUID, :uid)) AS MyFollowers,
  COUNT(NULLIF(BUID, :uid)) AS MeFollowing
FROM atable
WHERE :uid IN (AUID, BUID)
+2
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
0

More articles:


All Articles