All we need is an easy explanation of the problem, so here it is.
The problem
I’m trying to make a page for a website (with PostgreSQL database) that allows the owner of a group to add new members from their friends list.
Given the owner_id
of a Group
its group_id
I need to retrieve only the user_id
s from users who are both friends with the owner, and not in the group.
The relations being queried
I need to use the following relations
UserFriend
: Stores twouser_id
s to connect those two users from theUser
relation to each other as friend.UserGroup
: Stores auser_id
andgroup_id
to connect theGroup
relation to its members in theUser
relation.
The two relations are described as follows:
UserFriend
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
user_id_a | integer | | not null |
user_id_b | integer | | not null |
UserGroup
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------
user_id | integer | | not null |
group_id | integer | | not null |
My attempt so far with example data
For example, given the data below, I want to find all users that are friend with user 3, and not in group 3.
SELECT user_id_a, user_id_b FROM UserGroup UG, UserFriend UF
WHERE (UF.user_id_a = 3 AND UF.user_id_b != UG.user_id)
OR (UF.user_id_b = 3 AND UF.user_id_a != UG.user_id)
AND UG.group_id = 3 GROUP BY user_id_a, user_id_b;
Expected Result
user_id_a | user_id_b
-----------+-----------
1 | 3
Even better if it could just show something like
possible_members
------------------
3
Actual Result
user_id_a | user_id_b
-----------+-----------
1 | 3
3 | 2
3 | 4
Example data
UserFriend
user_id_a | user_id_b
-----------+-----------
1 | 2
1 | 3
1 | 4
3 | 4
3 | 2
UserGroup
user_id | group_id
---------+----------
1 | 1
2 | 1
3 | 1
4 | 1
1 | 2
2 | 3
3 | 3
4 | 3
Analysis
I’m fairly certain that the issue has something to do with the way I’m handling the fact that the user_id
I’m trying to match could be in either column of UserFriend
, but I’m not sure how to fix it. I’ve also tried using JOIN
s without any luck.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
Use set operations. If the user is 42, and the group is 1001, that would be
(SELECT user_id_a FROM userfriend
WHERE user_id_b = 42
UNION
SELECT user_id_b FROM userfriend
WHERE user_id_a = 42
)
EXCEPT
SELECT user_id FROM usergroup
WHERE group_id = 1001;
UNION
creates a result set that is the union of two query results and removes duplicate rows. EXCEPT
returns all result rows from the left side that do not occur on the right side.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0