Checking if entities match an id in on relation where it could show up in either of two columns

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_ids 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 two user_ids to connect those two users from the User relation to each other as friend.
  • UserGroup: Stores a user_id and group_id to connect the Group relation to its members in the User 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 JOINs 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

Tags: , ,

Leave a Reply