CODEWITHSUNDEEP
phpmysqlsql
HireLee
HireLee

Reputation: 573

SQL Following and Followers

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.

Upvotes: 2

Views: 3653

Answers (1)

jhinkley
jhinkley

Reputation: 668

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.

Upvotes: 1

Related Questions