members list SQL query whitout showing mutual friends, pending approval friends, blocked friends

user_5711057 0
Viewed 132

I have complex SQL QUERY I need some Help for the following conditions:

I'm looking for an SQL query with the below conditions:

SELECT *  FROM Table_1,Table_2 where user_id = ’16’ // 16 = ($_SESSION[ 'SESS_USER_ID' ])
  • CONDITION 1 -> DON’T SHOW MYSELF (CURRENT login user_id. => 16 (IT’s ME) ($_SESSION[ 'SESS_USER_ID' ]))
  • CONDITION 2 -> DON’T SHOW user_id 25, 26, 27, 28 in the list because they are already in table_2 for with some conditions for user_id =’16’ and CONDITION 0,1 from friend_status
  • CONDITION 3 -> ORDER BY signupdate

Sample of the result I am expecting below: WHAT I NEED-> I need the results showing all the members for login 'user_id' that are not in Table_2 'friend_status' CONDITION (0,1,2)

In the below table results, you can see that login 'user_id' (16 in this example) cannot see other 'user_id' that are in Table_2 CONDITION (0,1,2) including himself as the login 'user_id' (16)

//Perfect SQL results for me.
+—————————+—————————————+
+ user_id +signupdate   + 
+—————————+—————————————+
+   23    + 2020-11-30  +
+—————————+—————————————+
+   24    + 2020-11-30  +
+—————————+—————————————+
+   28    + 2020-11-30  +
+—————————+—————————————+
+   21    + 2020-11-30  +
+—————————+—————————————+

WHAT I NEED -> I need the an SQL QUERY to show results showing all the members for login 'user_id'(16) that are not in Table_2 'friend_status' CONDITION (0,1,2)

PROBLEM-> The Results I am getting below don't match the requested 'friend_status' CONDITION (0,1,2) of Table_2.

WHY? Because login 'user_id' should no see all the 'friend_status' CONDITION (0,1,2) in the full members page results that have NO CONDITION (0,1,2)

WHY? Because These SQl results CONDITION (0,1,2) are displayed elsewhere in 3 differents results pages for each CONDITION (0,1,2) -> Not todays topic.


//Wrong SQL results for me.
+—————————+—————————————+
+ user_id +signupdate
+—————————+—————————————+
+   16    + 2020-11-30  + <— SHOULD DO NOT SHOW, CONDITION-> ITS ME “login”  $user_id = ’16’
+—————————+—————————————+
+   23    + 2020-11-30  +
+—————————+—————————————+
+   24    + 2020-11-30  +
+—————————+—————————————+
+   25    + 2020-11-30  +<— SHOULD NOT SHOW -> TABLE_2  CONDITION user_id = ( $_SESSION[ 'SESS_USER_ID' ])=’16’ AND CONDITION2 friend_status= ‘0’(Pending approval Mutual friends)
+—————————+—————————————+
+   26    + 2020-11-30  +<— SHOULD NOT SHOW -> TABLE_2 CONDITION user_id = ( $_SESSION[ 'SESS_USER_ID' ])=’16’ AND CONDITION2 friend_status= ‘0’ (Pending approval Mutual friends)
+—————————+—————————————+
+   27    + 2020-11-30  +<— SHOULD NOT SHOW -> TABLE_2 CONDITION user_id = ( $_SESSION[ 'SESS_USER_ID' ])=’16’ AND CONDITION2 friend_status= ‘1’ (Mutual friends)
+—————————+—————————————+
+   28    + 2020-11-30  +<— SHOULD NOT SHOW -> TABLE_2 CONDITION user_id = ( $_SESSION[ 'SESS_USER_ID' ])=’16’ AND CONDITION2 friend_status= ‘2’ (Blocked)
+—————————+—————————————+
+   21    + 2020-11-30  +
+—————————+—————————————+

TABLE_2 (table for friends relationship) friend_status=‘0’ -> This means 'user_id' pending friendship acceptance by 'user_id_resquest' -> Do not Show in full members list for login 'user_id' friend_status=‘1’ -> This 'user_id' and 'user_id_resquest' are already mutual friends. -> Do not Show in full members list for login user_id friend_status=‘2’ -> This means 'user_id' is blocked by 'user_id_resquest' -> Do not Show in members list for current login user_id

+——————————————+—————————+——————————————+—————————————————+
+friendship_id + user_id + user_id_request + friend_status+
+——————————————+—————————+——————————————+—————————————————+
+   1          +   16    +        26    +        0        +//0->Pending
+——————————————+—————————+——————————————+—————————————————+
+   2          +   16    +        25    +        0        +//0->Pending
+——————————————+—————————+——————————————+—————————————————+
+   3          +   16    +        27    +        1        +//1->Mutual
+——————————————+—————————+——————————————+—————————————————+
+   4          +   21    +        27    +        1        +//1->Mutual
+——————————————+—————————+——————————————+—————————————————+
+   5          +   16    +        28    +        2        +//2 ->blocked
+——————————————+—————————+——————————————+—————————————————+

TABLE_1 (table of all the users)

+—————————+—————————————+
+ user_id + signupdate  +
+—————————+—————————————+
+   16    + 2020-11-30  + 
+—————————+—————————————+
+   23    + 2020-11-30  +
+—————————+—————————————+
+   24    + 2020-11-30  +
+—————————+—————————————+
+   25    + 2020-11-30  +
+—————————+—————————————+
+   26    + 2020-11-30  +
+—————————+—————————————+
+   27    + 2020-11-30  +
+—————————+—————————————+
+   28    + 2020-11-30  +
+—————————+—————————————+
mysql sql php
3 Answers

I am not sure which of the following will perform better with your real project data, so I recommend that you benchmark both to see if one performs noticeably better than the other.

Codes: (Demo)

  1. Subquery on every row of Table_1 needing 2 placeholders in a prepared statement (might not perform well)

    SELECT user_id, signupdate
FROM Table_1 AS t1
WHERE user_id != 16
  AND NOT EXISTS(
            SELECT 1
            FROM Table_2
            WHERE user_id_request = t1.user_id
              AND user_id = 16
          );

  2. Left join needing only 1 place holder in a prepared statement:

    SELECT t1.user_id, t1.signupdate
FROM Table_1 AS t1
LEFT JOIN Table_2 AS t2 ON t1.user_id = t2.user_id_request
WHERE 16 NOT IN (t1.user_id, COALESCE(t2.user_id,0))
  AND t2.friendship_id IS NULL;

Both output:

| user_id | signupdate |
| ------- | ---------- |
| 23      | 2020-11-30 |
| 24      | 2020-11-30 |
| 21      | 2020-11-30 |
user_29434030

First of all, get rid of the comma-join and replace it with a JOIN; a LEFT JOIN in particular. Besides, you're basically doing a CROSS JOIN by not adding a WHERE on matching columns between the two tables. I'm not sure if that's what you intended to do though.

Try with this query:

SELECT COALESCE(t2.user_id,t1.user_id) AS user_id,
      t1.signupdate,
      CASE 
           WHEN t2.user_id='16'/*($_SESSION[ 'SESS_USER_ID' ])*/ 
              AND friend_status IN (0,1) THEN 0
           WHEN t1.user_id='16'/*($_SESSION[ 'SESS_USER_ID' ])*/ THEN 0
           ELSE 1 END AS Chk
FROM Table_1 AS t1 LEFT JOIN Table_2 AS t2 
ON t1.user_id = t2.user_id_request 
HAVING Chk=1;

In the query, I've used LEFT JOIN on table_1's user_id column matching with table_2's user_id_request column. This is because if I match them between user_id in both table, table_2 will only show where user_id=16 and the rest as NULL, therefore those data that you want to filter from table_2 will not appear. In the SELECT .. section of the query, I've used two operations, COALESCE and a CASE expression. The CASE expression is used to filter out the current user_id and give it 0(zero) if it matches the condition and gives it 1 for other data that didn't match. This I assigned alias Chk and uses it at the end of the query on HAVING. For the COALESE part, I'm returning any user_id from table_2 first, then return user_id from table_1 if the table_2 is NULL.

Demo fiddle

Edit: Without using HAVING, check the CASE expression at WHERE like:

SELECT COALESCE(t2.user_id,t1.user_id) AS user_id,
      t1.signupdate
FROM Table_1 AS t1 LEFT JOIN Table_2 AS t2 
ON t1.user_id = t2.user_id_request
WHERE CASE 
           WHEN t2.user_id='16'/*($_SESSION[ 'SESS_USER_ID' ])*/ 
                   AND friend_status IN (0,1) THEN 0
           WHEN t1.user_id='16'/*($_SESSION[ 'SESS_USER_ID' ])*/ THEN 0
           ELSE 1 END=1;
user_109106920

It appears you basically need a left-join from the user table to the who is friended or pending. You are not clear if the person is marked as an ID = 2. If they are marked as blocked, would you not want their name to show up as a possible friend/friend request anyhow? So in short, you want anybody who is not your (current logged-in user) and has not already been requested as friend = 0, confirmed as friend = 1, OR already requested but BLOCKED user = 2. Why show someones name you wanted blocked.

select
      T1.*
   from
      Table1 T1
   where
          NOT T1.User_ID = ($_SESSION[ 'SESS_USER_ID' ])
      AND T1.User_ID NOT IN ( select T2.user_id_request
                                 from Table2 T2
                                 where T2.User_ID = ($_SESSION[ 'SESS_USER_ID' ])) 
   order by 
      T1.SignupDate

Just make sure you are properly parameterizing your queries and not using direct variables to build your query strings. You will be wide-open to sql-injection.

user_741950
Related