MySQL Query – Pending Friends Approval / Relationship between user & friends

I have two tables:

• g_relationships (id,request_id,receiver_id,status,approved)
• g_users (which contains: id,username,…)

I have a hard time making a query that adds a temporary column, checks whether userid: 4 exists in request_id or receiver_id column, and take the other userid and fetch username from g_users and adds it to the temporary column.

For example: Lets say I have userid 4. I then want to select g_relationships, and left join with g_users, and add a column that checks whether my userid (4) is in the request_id or the receiver_id column, and takes userid which isn’t mine, and checks which username that it is and fills the new column with.

For example: a correct result would look like this: (if I were to receive a friend request)

id	request_id	receiver_id	status	approved	friend(new_column)
1	3	4	friend	no	Neo

But if I was the one who sent the friend request it would look like this

id	request_id	receiver_id	status	approved	friend(new_column)
1	4	3	friend	no	Neo

In other words, the query should always check in what column that I don’t appear , and take that userid, and check against g_users table and place the username from there into friend column.

This is what I have come up with so far, but this only checks then request_id is not 4, and also doesn’t populate the temporary column “friend” with the username :/

    SELECT *, ("friend" = CASE WHEN request_id != 4 THEN  END) as friend_id  
    FROM `g_relationships`
    LEFT JOIN g_users
    ON g_relationship.request_id = g_users.id
    WHERE request_id = 4 OR receiver_id = 4
    AND status = "friend"
    AND approved = "no"

Any suggestions?

Answer

I wrote this two ways. Both use an extra JOIN to the g_users table. The first is a single query that gets you the output you’re looking for, while the the second is (in my opinion) more readable but uses a UNION and is technically two queries.

They give the same output.

Sandbox setup:

CREATE TABLE g_relationships (
    id INT PRIMARY KEY,
    request_id INT,
    receiver_id INT,
    status VARCHAR(10),
    approved VARCHAR(10)
);

CREATE TABLE g_users (
    id INT PRIMARY KEY,
    username VARCHAR(20)
);

INSERT INTO g_users (id, username) VALUES
(1, "Jane"),
(2, "Bob"),
(3, "Sally"),
(4, "Frank");

INSERT INTO g_relationships (id, request_id, receiver_id, status, approved) VALUES
(1, 1, 3, "Friend", "No"),
(2, 1, 4, "Enemy", "No"),
(3, 4, 3, "Friend", "Yes"),
(4, 4, 2, "Friend", "Yes");

Mega-JOIN:

SELECT u.id, rel.request_id, rel.receiver_id, rel.status, rel.approved, u2.username AS FriendName
FROM g_relationships AS rel
JOIN g_users AS u
  ON (rel.request_id = u.id OR rel.receiver_id = u.id)
JOIN g_users AS u2
  ON (rel.request_id = u2.id OR rel.receiver_id = u2.id)
WHERE u.id != u2.id
  AND u.id = 4;

UNION:

SELECT u.id, rel.request_id, rel.receiver_id, rel.status, rel.approved, u2.username AS FriendName
FROM g_relationships AS rel
JOIN g_users AS u
  ON rel.request_id = u.id
JOIN g_users AS u2
  ON rel.receiver_id = u2.id
WHERE u.id = 4

UNION

SELECT u.id, rel.request_id, rel.receiver_id, rel.status, rel.approved, u2.username AS FriendName
FROM g_relationships AS rel
JOIN g_users AS u
  ON rel.receiver_id = u.id
JOIN g_users AS u2
  ON rel.request_id = u2.id
WHERE u.id = 4;