sql statement sorting from other table with NOT IN

Question

I am designing a simple microblog website for a class and need some help with an sql statement for my scrolling pagination.

I have 2 tables: User and Follows.

In my 'User' table I have uid, FirstName, LastName, Email, and Username.

I have another 'Follows' table which has rid, FollowedName, and FollowingName.

For example, if username "Alex" followed username "Bob" then a new row would exist with FollowedName="Bob" and FollowingName="Alex".

For a search page, I allow the user to sort based on popularity (which is based on the number of followers). So I need an SQL statement which will select all of the rows from 'User' and then order them based on how many entries in the 'Follows' table each user has.

On top of this, I need the statement to filter out those users whose uid is in a string name $explodedids.

I have everything working but I cannot figure out where to put the "WHERE uid NOT IN (".$explodedids.")".

Here is my statement which returns properly but does not filter out $explodedids:

"SELECT Username, FirstName, LastName, Email, COUNT( Followingname ) AS count 
 FROM (SELECT u.Username, u.FirstName, u.LastName, u.Email, f.Followingname 
       FROM User AS u LEFT JOIN Follows AS f ON u.Username = f.Followingname) 
 AS T GROUP BY Username ORDER BY count DESC LIMIT ".$postnumbers

$postnumbers is simply the limit number on my scrolling pagination. I am sure that I have just been putting the WHERE NOT IN in the wrong place but if you guys could help me out that would be awesome.

Answer 1

Here is a select statement that will meet your requirements:

--Test Data
CREATE TABLE #User (  uid int
                   , FirstName varchar(50)
                   , LastName varchar(50)
                   , Email varchar(50)
                   , Username  varchar(50));

INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(1,'MackF','MackL','[email protected]','MackUname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(2,'2F','2L','[email protected]','2Uname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(3,'3F','3L','[email protected]','3Uname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(4,'4F','4L','[email protected]','4Uname');

CREATE TABLE #Follows( rid int
                    , FollowedName varchar(50)
                    , FollowingName varchar(50));

INSERT INTO #Follows VALUES(1,'4Uname','2Uname');
INSERT INTO #Follows VALUES(2,'MackUname','4Uname');
INSERT INTO #Follows VALUES(3,'4Uname','3Uname');
INSERT INTO #Follows VALUES(4,'MackUname','3Uname');

--Query
SELECT u.Username
     , u.FirstName
     , u.LastName
     , u.Email
     , COUNT( f.Followingname ) AS followercount 
FROM #User u 
LEFT JOIN #Follows f 
   ON u.Username = f.Followingname
WHERE uid NOT IN (2,4)--here is where your WHERE clause goes remove this line to see all records
GROUP BY u.Username
ORDER BY followercount DESC

--Results
USERNAME    FIRSTNAME   LASTNAME    EMAIL           FOLLOWERCOUNT
3Uname      3F          3L          [email protected]      2
MackUname   MackF       MackL       [email protected]   0

Here is a SQL fiddle to try it all out.

NB: I have renamed your column "count" to "followercount" as COUNT is a reserved word.

Answer 2

"SELECT Username, FirstName, LastName, Email, 
 COUNT( Followingname ) AS count 
 FROM (
 SELECT u.Username, u.FirstName, u.LastName, u.Email, f.Followingname 
       FROM User AS u 
       LEFT JOIN Follows AS f 
       ON u.Username = f.Followingname 
       WHERE u.id NOT IN($explodids)
 ) AS T 
 GROUP BY Username 
 ORDER BY count DESC 
 LIMIT $postnumbers";

Assuming that $explodids is a string in format 'x,y,z'