SQL Following and Followers

Question

Ok I have two tables, one named account_members and another called account_follows. I want a Twitter style following system where account_members can follow each other.

Account Follows Table Structure:

id
account_name
followed_name
time

Account Members Table Structure:

id
Account_name
status (Account active or not)

I thought I could get away with just one simple query to get all the accounts being followed:

public function following($account_name)
{
    $sql = "SELECT 

    F.id, F.account_name, F.followed_name, F.time, 
    M.account_name AS session_name, M.status 

    FROM account_follows F
    LEFT JOIN account_members M ON F.account_name = M.account_name

    WHERE F.account_name = :account_name 
    AND M.account_name = :account_name

    ORDER BY id DESC LIMIT 5";
}

This will display all the account_members which are being followed (the $account_name is set via the url)

The issue that I have is allowing the logged in account_member to be able to Follow or Unfollow friends of friends who they are following. I do a simple check for the logged in account_member to unfollow anyone on their list by doing the following:

if($_SESSION['account_name'] == $row['account_name'])
{
    echo'<a href="" id="..." class="...">Unfollow</a>';
}

The above works fine, but I want to do something similar with the logged in accounts followers followers... If that makes sense?

So Bob is logged in, and Bob looks at his following list and clicks on mike and views who mike is following, and from this list has the ability to follow/unfollow people mike is following (and some of which Bob could be following)

Any help or guidance is appreciated.

jhinkley · Accepted Answer · 2013-03-19T18:34:34.897

1

The query you have will work for any member's account name passed in, but the query itself does not take into account the currently logged in member's follows, so you need to join in their data to it.

The query returns a list of members that the url specified account is following. With that is a bit that tells whether the logged in user is also following that member. Use that bit to decided whether you need to echo a follow or unfollow link.

SELECT 
        theirFollows.id, theirFollows.account_name, 
        theirFollows.followed_name, theirFollows.time, 
        M.account_name AS member_name, M.status, 
        case 
            when myFollows.followed_name is null then 0
            else 1
        end as sessionMemberIsFollowing
FROM    account_members M
        LEFT JOIN account_follows theirFollows
          ON theirFollows.account_name = M.account_name
        LEFT JOIN 
            (
                select followed_name
                from account_follows 
                where account_name = :session_account_name
            ) myFollows
            on myFollows.followed_name = theirFollows.followed_name

WHERE   M.account_name = :account_name

One of your select columns was labled session_name, but that's a bit misleading since the account_name passed in comes from the url. Also, only one of you where clauses is needed since that is column you are joining on.

edited Mar 19 '13 at 18:34

answered Mar 19 '13 at 13:27

jhinkley

668
4
10

Hi @jhinkley thanks for your time trying to work this... Looking at this I think there will be an error... As 'F.' isnt set anywhere within the from or left join... Would this be myfollows or theirfollows? – HireLee Mar 19 '13 at 14:24
Hey that seemed to work fine with the edit... The only issue is as soon as $account_name is changed from the Session_account_name no results are returned... Seems odd... Do you know why this could be happening? – HireLee Mar 19 '13 at 18:10
You my friend are a life saver... It is seemingly working fine now... I spent the whole day trying to work this out... is this is the most elegant and efficient solution? Also I have never seen the 'case when else end as' before, what exactly does this do? Just for my future reference... I will mark the answer as correct... Good job buddy. – HireLee Mar 19 '13 at 18:42
1

Case is like a switch statement. You define conditions (when) and their respective outputs (then). Since you're left joining on the myFollows subquery, the myFollows.followed_name could be null. The case checks to see if it's null (meaning the session_account has no matching records) and then assigns it a value of 0, otherwise set it to 1. You can have multiple 'when' conditions within a case if your logic requires it. – jhinkley Mar 19 '13 at 18:53

SQL Following and Followers

1 Answers1