Return Most Followed Users NOT already following (or viewed)

Question

Trying to create a query that does the following:

A. Sorts all users in order of how many followers they have
B. Remove the users that the person making the request is already following
C. Remover the users that the person making the request has already seen

Here is my attempt:

SELECT COUNT( f.FOLLOWING_UID_FK ) AS Records , u.uid, u.full_name, u.bio, u.thumb_img, u.college, u.HEADER_IMG 
   FROM users AS u, FOLLOWERS AS f 
WHERE u.uid <>1580
AND NOT u.uid IN ( SELECT UID_FK FROM FRIEND_FINDER_SWIPES WHERE UID_FK <>1580 ) 
AND NOT u.uid IN ( SELECT UID_FK FROM FOLLOWERS WHERE FOLLOWING_UID_FK <>1580 ) 
AND u.uid = f.FOLLOWING_UID_FK GROUP BY f.FOLLOWING_UID_FK ORDER BY records DESC LIMIT 0 , 30

Whats weird is this query works if broken up for example this will produce a list of all users in order of most followed :

SELECT COUNT( f.FOLLOWING_UID_FK ) AS Records, u.uid, u.full_name, u.bio, u.thumb_img, u.college, u.HEADER_IMG
FROM users AS u, FOLLOWERS AS f
WHERE u.uid <>1580
AND u.uid = f.FOLLOWING_UID_FK
GROUP BY f.FOLLOWING_UID_FK
ORDER BY records DESC
LIMIT 0 , 30

And this will return 30 users that the person making the request is not already following and has not seen already:

   SELECT  u.uid, u.full_name, u.bio, u.thumb_img, u.college, u.HEADER_IMG 
       FROM users AS u
    WHERE u.uid <>1580
    AND NOT u.uid IN ( SELECT UID_FK FROM FRIEND_FINDER_SWIPES WHERE UID_FK <>1580 ) 
    AND NOT u.uid IN ( SELECT UID_FK FROM FOLLOWERS WHERE FOLLOWING_UID_FK <>1580 ) 
   DESC LIMIT 0 , 30

But when you combine the 2 the results seem off to say the least. Why?

Answer 1

Your logic seems confused, as least relevant to the goals you have in mind.

First, you want to count the number of followers that users have. You can do that in a subquery. Then you want to remove people with a relationship to the current user. The NOT IN . . . <> has two double negatives. That doesn't seem right. I'm not quite sure what the direction is for the subqueries on the WHERE clause; I'm leaving the logic pretty much as in your question.

Also, learn to use proper JOIN syntax

SELECT uf.NumFollowers, u.uid, u.full_name,
       u.bio, u.thumb_img, u.college, u.HEADER_IMG 
FROM (SELECT UID_FK, COUNT(*) as NumFollowers
      FROM FOLLOWERS f 
      GROUP BY UID_FK
     ) uf JOIN
     users u
     ON uf.uid = u.FOLLOWING_UID_FK
WHERE u.uid <> 1580 AND
      u.uid NOT IN (SELECT UID_FK FROM FRIEND_FINDER_SWIPES WHERE UID_FK = 1580 ) AND
      u.uid NOT IN (SELECT UID_FK FROM FOLLOWERS WHERE FOLLOWING_UID_FK = 1580 ) 
GROUP BY u.uid
ORDER BY NumFollowers DESC
LIMIT 0, 30

Answer 2

It appears I answered my own question, I was using NOT instead of <>ANY this was what i came up with and it appears to be working :

SELECT COUNT( f.FOLLOWING_UID_FK ) AS Records, u.uid, u.full_name, u.bio, u.thumb_img, u.college, u.HEADER_IMG
FROM users AS u, FOLLOWERS AS f
WHERE u.uid <>1580
AND u.uid <> ANY(

SELECT UID_FK
FROM FRIEND_FINDER_SWIPES
WHERE UID_FK <>1580
)
AND u.uid <> ANY(

SELECT UID_FK
FROM FOLLOWERS
WHERE FOLLOWING_UID_FK <>1580
)
AND u.uid = f.FOLLOWING_UID_FK
GROUP BY f.FOLLOWING_UID_FK
ORDER BY records DESC
LIMIT 0 , 30