CODEWITHSUNDEEP
mysqlsql
user397916
user397916

Reputation:

get followers twitter like using MySQL

hello lets say i have an example

my follow table

A B
1 2 // same so its friend
2 1 // same so its a friend
1 3 // user 1 is following 3
1 4 // user 1 is following 4

etc

lets say we are the user 1 how can we list our friends ? i have something in my head like

SELECT COUNT(*) FROM social WHERE ((A = B) = (B = A)) as friends
// so it will be something like count friends where ( 1 = 2 ) = ( 1 = 2) if you get my logic

or can we do that somehow ?

if it work it will count as 1

Upvotes: 2

Views: 1379

Answers (1)

Dancrumb
Dancrumb

Reputation: 27529

This should do it:

SELECT COUNT(me.A) FROM social AS me 
   INNER JOIN social AS you ON me.B = you.A 
WHERE me.A = you.B AND me.A = 1

Remove the COUNT if you want a list of friends.

EDIT

As requested, an explanation.

You're JOINing a table to itself because you're interested in the relationships between rows.

I decided to alias the tables as me and you to make the relationship clear. What that is saying is that column A can refer to me as the follower or you as the follower. Column B refers to the followee

If you were to rename the columns, the query would read more clearly

if A -> follower and B -> followee, we'd have:

SELECT COUNT(me.follower) FROM social AS me 
   INNER JOIN social AS you ON me.followee = you.follower
WHERE me.follower = you.followee AND me.follower = 1

So it's saying, take two copies of this table and JOIN the rows where the followee in me is the follower in you. Then, filter and show only the rows where the follower in me is the followee in you... there by capturing your desire to have (A == B) && (B == A)

Perhaps the table aliases aren't that great, but I hope that clarifies a little.

SECOND EDIT Per comments below, a clearer form may be:

SELECT COUNT(me.A) FROM social AS me 
   INNER JOIN social AS you ON me.A = you.B AND me.B = you.A
WHERE me.A = 1

Upvotes: 6

Related Questions