how compare records and select them in third column in same table in mysql

Question

Table Interest_log

    Id  user_id  user_id_interested     
------  -------  ------------------  
     3        2                   3   
     5        3                   2   
     6        6                   2   
     7        6                   3   
     8        7                   2    
     9        2                   6 

I'm selecting those users where user_id_interested =2 very simple select a.user_id ,a.user_id_interested from interest_log a where a.user_id =2;

when I select those users where user_id_interested = 2 and in new column I select those user_id who also liked back to the user who like him.

Expecting result

     Id  user_id  user_id_interested     bUser_id
------  -------  ------------------     ----------- 
     5        3                   2       null
     6        6                   2        2
     8        7                   2       null

This is about who like user 2 and In bUserId, first row and third row has null while second row has value 2 because user_id 2 also liked user_id 6 but user 2 didn't like user 3, 7 so there is null

I wrote this query

SELECT a.user_id ,a.user_id_interested , b.user_id AS bUserId
FROM interest_log a
LEFT OUTER JOIN interest_log b
ON a.user_id_interested = b.user_id
WHERE a.user_id_interested =2
AND b.user_id = a.user_id_interested

but it's giving following result

user_id  user_id_interested  bUserId  
-------  ------------------  ---------
      3                   2          2
      3                   2          2
      6                   2          2
      6                   2          2

any idea what join should be used here. i'm really thankful if someone can tell what join should be used and how to write tat join it in cakephp 3 .

Answer 1

You could try joining on a sub query:

SELECT a.user_id ,a.user_id_interested, b.bUserId
FROM interest_log a
LEFT JOIN (
    SELECT a.user_id_interested AS bUserId FROM interest_log a WHERE a.user_id = 2
) as b ON a.user_id = b.bUserId
WHERE a.user_id_interested = 2

The query is asking to find the users who are interested in user_id 2, it is then joining bUserId against a sub query which is asking to find only the users that user_id 2 is interested in. Every time there is a match you get a result, and every time there isn't you get NULL.

This will give you a table that is similar to what you wanted:

+---------+--------------------+----------+
| user_id | user_id_interested | bUserId |
+---------+--------------------+----------+
|       3 |                  2 | 3        |
|       6 |                  2 | 6        |
|       7 |                  2 | NULL     |
+---------+--------------------+----------+

To write this in Cakephp 3:

$id = 2;
$query = $this->InterestLog->find()
    ->select([
        'InterestLog.user_id',
        'InterestLog.user_id_interested',
        'b.bUserId'
    ])
    ->join([
        'b' => [
            'table' => '(SELECT InterestLog.user_id_interested as bUserId FROM interest_log InterestLog WHERE InterestLog.user_id = ' . $id . ')',
            'type' => 'LEFT',
            'conditions' => 'InterestLog.user_id = b.bUserId'
        ]
    ])
    ->where([
        'InterestLog.user_id_interested' => $id
    ]);