CODEWITHSUNDEEP
mysqlsqldatabasejoindatabase-design
Rafael Adel
Rafael Adel

Reputation: 7759

MySql get recommended people to follow

I want to get a list of friends that have been recommended to me based on the number of mutual friends we have.

Here's my schema:

I have a users table, which has a one to many relationship with the follow_users table.

follow_users {
    'follower_id', //many to one with users
    'followee_id', //many to one with users
    'is_approved'
}

How can I write a query that selects the users that have as many mutual friends as I do?

EDIT

I think I'm getting closer. I've wrote this query

$q = $this->em->createQuery("
        select recUser, count(recUser.id) as recUserCount
        from Zgh\FEBundle\Entity\User recUser
            inner join recUser.followees recFollowUser
            where recFollowUser.follower in (
                select mutual.id
                from Zgh\FEBundle\Entity\FollowUsers currentFollowUser
                inner join currentFollowUser.followee mutual
                where currentFollowUser.follower = :user
            )
        group by recUser.id
        order by recUserCount desc
    ");

Which returns the users I want, But also returns users that I've already followed, how to exclude already followed users ? (I've tried adding and recFollowUser.follower != :user just after the where statement but no good)

Upvotes: 0

Views: 308

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

This query will get user id's of friends of friends that a given :user_id has not friended yet. The list is ordered by # of mutual friends.

SELECT fu2.follower_id
FROM follow_users fu
JOIN follow_users fu2
    ON fu2.followee_id = fu.follower_id
    AND NOT EXISTS (SELECT 1 FROM follow_users fu3 
        WHERE fu3.followee_id = :user_id
        AND fu3.follower_id = fu2.follower_id)
WHERE fu.followee_id = :user_id
GROUP BY fu2.follower_id
ORDER BY COUNT(*) DESC

Upvotes: 1

Related Questions