CODEWITHSUNDEEP
phpmysqlsql
Red Balloon
Red Balloon

Reputation: 175

How to select pending friend requests from Database mysql

SQL: http://www.sqlfiddle.com/#!2/0f7a0d5/4

Table contains these rows:

INSERT INTO `friends` (`asker_user_id`, `asked_user_id`, `status`) VALUES (1,2,1);
INSERT INTO `friends` (`asker_user_id`, `asked_user_id`, `status`) VALUES (2,1,1);
INSERT INTO `friends` (`asker_user_id`, `asked_user_id`, `status`) VALUES (3,2,1);

This query selects two way friends of current user:

    SELECT f1.asked_user_id AS friend_id
        FROM friends AS f1 JOIN friends AS f2
        ON f1.asked_user_id = f2.asker_user_id
        AND f1.asker_user_id = f2.asked_user_id
    WHERE f1.status = 1 AND f2.status = 1
    AND f1.asker_user_id = 2

So it results in 1. Because user 2 has only user 1 as two way friend.

How do I show pending requests for user 2? Where user 2 is asked_user_id?

THis (3,2,1) is a pending request for user 2. Where 3 asked 2 to be friends. And for them to become two way friends new entry will be created wiht (2,3,1).

Upvotes: 0

Views: 1311

Answers (2)

echo_Me
echo_Me

Reputation: 37233

is this you looking for ?

this will show users who wants be friend with user 2 , so user 2 have 2 pending requests

 SELECT f1.asker_user_id AS friend_id
 FROM friends AS f1 
 WHERE f1.status = 1 
 AND f1.asked_user_id = 2

demo

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

You can get peding requests with the following:

SELECT f1.asker_user_id AS friend_id
FROM friends AS f1 
LEFT JOIN friends AS f2
    ON f1.asked_user_id = f2.asker_user_id
    AND f1.asker_user_id = f2.asked_user_id   
WHERE f1.status = 1 AND f2.status IS NULL
AND f1.asked_user_id = 2

sqlfiddle demo

Upvotes: 1

Related Questions