CODEWITHSUNDEEP
mysqlsqlrelationship
Justin Erswell
Justin Erswell

Reputation: 708

Reciprocal follow with user info

Hi I have two tables PAS_User and PAS_Follow see diagram below,

MySQL Tables involved

What I am struggling with is a query for MySQL where the user can only see a list of other users where the relationship id reciprocal between them.

For a reciprocal (Mutual) the PAS_Follow table will have to records in, so say we have a user with and id of 1 and a user with an id of 2 the table will contain two rows of data with row 1 being:

flow_follower_user_id = 1 && flow_followed_user_id = 2

and row 2 being

flow_follower_user_id = 2 && flow_followed_user_id = 1

I have this query however it seems to show users that are not following the source user.

SELECT DISTINCT PAS_Follow.folw_followed_user_id AS user_user_id, PAS_User.user_first_name, PAS_User.user_last_name, PAS_User.user_avatar_url, PAS_User.user_sector, PAS_User.user_job_type, PAS_User.user_pass_token FROM PAS_User RIGHT OUTER JOIN PAS_Follow ON PAS_User.user_user_id = PAS_Follow.folw_followed_user_id WHERE `folw_followed_user_id` =**:id** OR `folw_follower_user_id` =**:id** AND `folw_deleted` = 0 ORDER BY PAS_Follow.folw_followed_user_id ASC

Where **:id** = 1 in this example.

This is for a direct message platform so it is important that when a user initiates the list of user they only see user that have a reciprocal relationship.

Any help would be gratefully received and I hope that the explanation of the problem is complete enough.

**** ANSWER FROM BELOW ****

Hope this helps someone else

SELECT foll_second.folw_follower_user_id AS user_user_id, user.user_first_name, user.user_last_name, user.user_avatar_url, user.user_job_type FROM PAS_Follow foll_first, PAS_Follow foll_second, PAS_User user WHERE foll_first.folw_follower_user_id = foll_second.folw_followed_user_id AND foll_second.folw_follower_user_id = foll_first.folw_followed_user_id AND foll_first.folw_follower_user_id =:id AND foll_second.folw_follower_user_id = USER .user_user_id

Upvotes: 0

Views: 120

Answers (1)

Gijs
Gijs

Reputation: 10891

You're missing an id in the user's table. If you have that, and let's call it id, you can use this:

SELECT * 

    foll_second.folw_follower_user_id AS user_user_id, 
    user.user_first_name, 
    user.user_last_name, 
    user.user_avatar_url, 
    user.user_job_type 

FROM 

    PAS_Follow foll_first,
    PAS_Follow foll_second,
    PAS_User user

WHERE 

   foll_first.folw_follower_user = foll_second.folw_followed_user 
   AND foll_second.folw_follower_user = foll_first.folw_followed_user
   AND foll_first.folw_follower_user = **:id**
   AND foll_second.folw_follower_user = user.id;

Which will give you a list of combinations of the two linking relations, and the user that is connected to **:id** by those relations.

This selects from all possible combinations of two follow-relations and a user those that correspond to something you want, which means:

  • The two relations should connect to each other.
  • That should be reciprocal actually
  • The 'starting' user should be the one you're looking for
  • The user row that's also in the result should be the one that the second relation points to.

Upvotes: 1

Related Questions