sql query to get mutal friends based on email

问题: I have sample data that looks like this (complete schema and data on SQL fiddle): | users | | user_id | email | |---------...

问题:

I have sample data that looks like this (complete schema and data on SQL fiddle):

| users                              |
| user_id | email                    |
|---------|--------------------------|
| 1       | ashutosh8657@example.com |
| 2       | Kanchhi@example.com      |
| 3       | modi@example.com         |
| 4       | andy@example.com         |
| 5       | maya@example.com         |
| 6       | jetli@example.com        |
| 7       | john@example.com         |

| user_relations                                                          |
| user_relation_id | requestor_user_id | receiver_user_id | friend_status |
|------------------|-------------------|------------------|---------------|
| 1                | 2                 | 4                | 1             |
| 2                | 2                 | 6                | 1             |
| 3                | 2                 | 7                | 1             |
| 4                | 5                 | 2                | NULL          |
| 5                | 5                 | 7                | NULL          |
| 6                | 7                 | 2                | NULL          |
| 7                | 7                 | 4                | 1             |
| 8                | 7                 | 5                | 1             |
| 9                | 7                 | 6                | 1             |
| 10               | 4                 | 2                | 1             |
| 11               | 4                 | 3                | 1             |
| 12               | 4                 | 5                | 1             |
| 13               | 4                 | 6                | 1             |
| 14               | 4                 | 7                | 1             |

If input is these two emails:

Kanchhi@example.com, john@example.com 

Then my expected expected output is this (order does not matter):

andy@example.com
jetli@example.com 

In the above example, the friends of user id 2 is user ids (4, 6, 7) and friend of user id 7 is user ids (2, 4, 5, 6). So the mutual friend of user id 2 and 7 is 4 and 6. I need email address of mutual user id.

Another example input is:

andy@example.com, john@example.com

Then expected output is this:

jetli@example.com
Kanchhi@example.com
maya@example.com` 

In the above example, friend of user id 4 is (2, 6, 5, 3, 2) and friend of user id 7 is (6, 5, 2, 4). So mutual friend user id will be 2, 6, 5. I need email address of these user id in output.

Query 1- I tried but got wrong result:

SELECT  u.email 
       FROM user_relations r 
        LEFT JOIN users u   ON r.requestor_user_id = u.user_id
        LEFT JOIN users z   ON r.receiver_user_id = z.user_id
       where u.email in ('Kanchhi@example.com','john@example.com') or 
       z.email in ('Kanchhi@example.com','john@example.com') 
       and r.friend_status = 1 
       group by u.email 
      having count(u.email ) > 1

Results- but not correct:

| email               |
|---------------------|
| andy@example.com    |
| john@example.com    |
| Kanchhi@example.com |

How to get this?


回答1:

You can start from the user, then join the relations.

Then group on the receiver & requestor that are different from the user.

SELECT 
case when u.user_id = r.receiver_user_id then requestor.email else receiver.email end as friend_email
FROM users u
JOIN user_relations r 
  ON (r.requestor_user_id = u.user_id OR r.receiver_user_id = u.user_id)
 AND r.friend_status = 1 
LEFT JOIN users requestor ON requestor.user_id = r.requestor_user_id
LEFT JOIN users receiver ON receiver.user_id = r.receiver_user_id
WHERE u.email in ('Kanchhi@example.com','john@example.com') 
GROUP BY friend_email
HAVING COUNT(DISTINCT u.user_id) > 1

Result:

friend_email
----------------
andy@example.com 
jetli@example.com 

回答2:

The crux of the problem is building a list of users that are friends of Kanchi and John or have them as friends. Then count those users that appear in the list twice:

-- SELECT email FROM users WHERE userid IN (
SELECT friendid
FROM (
    SELECT requestor_user_id AS userid, receiver_user_id AS friendid
    FROM user_relations
    WHERE friend_status = 1 AND requestor_user_id IN (
        SELECT user_id
        FROM users
        WHERE email IN ('Kanchhi@example.com','john@example.com')
    )

    UNION ALL

    SELECT receiver_user_id, requestor_user_id
    FROM user_relations
    WHERE friend_status = 1 AND receiver_user_id IN (
        SELECT user_id
        FROM users
        WHERE email IN ('Kanchhi@example.com','john@example.com')
    )
) AS X
GROUP BY friendid
HAVING COUNT(DISTINCT userid) = 2

Matching the results with users is trivial. For your sample input the result is:

4    Andy     andy@example.com     ashutosh    2019-01-11 13:34:05
6    jetli    jetli@example.com    ashutosh    2019-01-11 13:34:05

回答3:

You could use this and use index in join coulmn for performance

SELECT DISTINCT u.email
FROM
(
    SELECT r.receiver_user_id AS id, u.email
    FROM user_relations r 
    INNER JOIN users u
    ON r.requestor_user_id = u.user_id 
    WHERE r.friend_status = 1 AND u.email = 'Kanchhi@example.com'
    UNION ALL
    SELECT r.requestor_user_id AS id, u.email
    FROM user_relations r 
    INNER JOIN users u
    ON r.receiver_user_id = u.user_id 
    WHERE r.friend_status = 1 AND u.email = 'Kanchhi@example.com'
) k
INNER JOIN
(
    SELECT r.receiver_user_id AS id, u.email
    FROM user_relations r 
    INNER JOIN users u
    ON r.requestor_user_id = u.user_id 
    WHERE r.friend_status = 1 AND u.email = 'john@example.com'
    UNION ALL
    SELECT r.requestor_user_id AS id, u.email
    FROM user_relations r 
    INNER JOIN users u
    ON r.receiver_user_id = u.user_id 
    WHERE r.friend_status = 1 AND u.email = 'john@example.com'
) j
ON k.id = j.id
INNER JOIN users u 
ON k.id = u.user_id 
AND u.email NOT IN ('Kanchhi@example.com','john@example.com');

回答4:

Try this

SELECT usu.email from users usu where usu.user_id in (
select          r.receiver_user_id
from sov.user_relations r 
where r.requestor_user_id in (
    SELECT  u.user_id from users u where u.email in ('Kanchhi@example.com','john@example.com') 
     )
and r.friend_status = 1      
group by r.receiver_user_id
having count(r.receiver_user_id)  > 1
);

回答5:

you can create extra function like below code

BEGIN
DECLARE limitCount INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE res INT DEFAULT 0;
DECLARE temp TEXT;
SET limitCount = 1 + LENGTH(inputList) - LENGTH(REPLACE(inputList, ',',''));
simple_loop:LOOP
SET counter = counter + 1;
SET temp = SUBSTRING_INDEX(SUBSTRING_INDEX(inputList,',',counter),',',-1);
SET res = FIND_IN_SET(temp,targetList);
IF res > 0 THEN LEAVE simple_loop; END IF;
IF counter = limitCount THEN LEAVE simple_loop; END IF;
END LOOP simple_loop;
RETURN res;
END

AND USE THIS FUNCTION LIKE THIS

find_in_set_extra('Kanchhi@example.com, john@example.com','john@example.com') OR WHAT EVER YOUR INPUTS AND OUTPUTS.
  • 发表于 2019-01-16 13:25
  • 阅读 ( 207 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除