CODEWITHSUNDEEP
mysql
ShoeLace1291
ShoeLace1291

Reputation: 4698

How can I use MySQL to get a list of friends based on the status of a friend request?

I have two tables: members and member_requests. When a member sends another member a friend request, a row is inserted into the member_requests table. If the recipient accepts the request, the status is updated to 1. If a person denies the request, it will be deleted. I have tried many different queries to generate a list of friends for a certain user, but none seem to work correctly. Either the author_id of the request has to be the id of the current member, or the recipient id has to be the id of the current member. Can someone provide a query that could do this?

http://sqlfiddle.com/#!2/021d7/13 

                   SELECT
                        r.request_id, r.author_id, r.recipient_id, r.status,
                        m.member_id, m.display_name
                    FROM member_requests AS r
                    LEFT JOIN members AS m ON (r.author_id = m.member_id OR r.recipient_id = m.member_id)
                    WHERE r.status = 1
                    ORDER BY m.display_name

Upvotes: 0

Views: 68

Answers (1)

AdamMc331
AdamMc331

Reputation: 16690

This doesn't sound too bad at all. What you could do is get a list of all times when the user has sent a request and it was accepted, as well as a list of all times a user has received a request and it was accepted, and union those two results together.

Try this:

(SELECT recipient_id
FROM member_requests 
WHERE author_id = 1 AND status = 1) 
UNION
(SELECT author_id
FROM member_requests
WHERE recipient_id = 1 AND status = 1);

Here is your SQL Fiddle back.

Upvotes: 1

Related Questions