Как вывести список общих друзей?

Question

[Александр]

Здравствуйте!
Есть 2 таблицы:
users: friends:

Необходимо вывести общих друзей пользователей Саши (id 1) и Димы (id 49519). Исходя из таблицы, общие друзья у них Петя (id 2) и Вася (id 3).
Список всех друзей пользователя, вывожу таким запросом:

$id  = '49519'; # id пользователя
$userid = '1'; # Мой id

$rows = $db->selectPage($total, "SELECT * FROM ?_friends f1
                                         LEFT JOIN ?_friends f2 ON (f1.friend1 = f2.friend2 AND f2.friend1 = f1.friend2) 
                                         LEFT JOIN ?_users u ON (f1.friend2 = u.user_id OR f2.friend1 = u.user_id)
                                         WHERE f1.friend1 = ".$id."
                                         UNION 
                                         SELECT * FROM ?_friends f1
                                         RIGHT JOIN ?_friends f2 ON (f1.friend1 = f2.friend2 AND f2.friend1 = f1.friend2)
                                         LEFT JOIN ?_users u ON (f1.friend1 = u.user_id OR f2.friend1 = u.user_id)
                                         WHERE f2.friend2 = ".$id.");

Гуглил по этой теме, находил обсуждения, но не получается подогнать под свой запрос.
Подскажите пожалуйста, как мне переделать запрос, чтобы вывести список общих друзей?
Заранее благодарю!

Answer 1

[Игорь]

Если совсем не оптимизировано то вот так как пример:

select * from users where id in (
select f1 from (
select friends.friend1 as f1 from friends where friends.friend2 = 1
UNION ALL
select friends.friend2 from friends where friends.friend1 = 1) as s1
where f1 in (
select friends.friend1 from friends where friends.friend2 = 49519
UNION ALL
select friends.friend2 from friends where friends.friend1 = 49519
) )

Если нужно оптимизировано, то покрутить explain

Comments

[Александр]

Спасибо Вам, Игорь!
А у Вас есть возможность написать оптимизированный вариант?

[Игорь]

Александр:
нужно пробовать на реальных данных. можете вот такой вариант попробовать

select * from users where id in (
select f1 from (
select friends.friend1 as f1 from friends where friends.friend2 = 1
UNION ALL
select friends.friend2 from friends where friends.friend1 = 1) as s1
INNER JOIN (
select friends.friend1 as f2 from friends where friends.friend2 = 49519
UNION ALL
select friends.friend2 from friends where friends.friend1 = 49519
) as s2 ON s1.f1 = s2.f2)

[Игорь]

Александр: и вот так

select * from users where id in (
select f1 from 
(select if(friend1 = 1, friend2, friend1) as f1 from friends where (friend1 = 1 or friend2 = 1)) as s1
INNER JOIN 
(select if(friend1 = 49519, friend2, friend1) as f2 from friends where (friend1 = 49519 or friend2 = 49519)) as s2
on s1.f1 = s2.f2
)

В зависимости от используемых индексов один из этих 3 запросов будет наиболее быстрым. Прогоняете на реальных данных с замером производительности и выбираете лучший.

[Игорь]

Александр: и да, список всех друзей пользователя вместо вашего запроса можно получить так:

select f1 from (
select friend2 as f1 from friends where friend1 = 1
UNION 
select friend1 as f1 from friends where friend2 = 1
) as s1

или так

select if(friend1 = 1, friend2, friend1) as f1
from friends 
where (friend1 = 1 or friend2 = 1)

[Александр]

Игорь: Игорь, спасибо Вам большое! Все запросы работают, выбрал второй запрос, так как быстрее выполняется.

[Александр]

А вот по поводу списка всех друзей, подскажите а как правильно в запросе связать с таблицей users?

[Игорь]

Александр:
Смотрите по аналогии

SELECT * FROM users 
WHERE id IN (
select if(friend1 = 1, friend2, friend1) as f1
from friends 
where (friend1 = 1 or friend2 = 1)
)

[Александр]

Игорь: Еще раз спасибо! Очень выручили.

[Александр]

Похожий запрос, только тут используется еще одна таблица users_visit, где пишется пишется время когда пользователь заходил на сайт, в таблице 3 поля:id, visit_id и visit_now_time.

$rows = $db->selectPage($total, "(SELECT * FROM friends f1
                                             LEFT JOIN friends  f2 ON (f1.friend1 = f2.friend2 AND f2.friend1 = f1.friend2) 
                                             LEFT JOIN users u ON (f1.friend2 = u.user_id OR f2.friend1 = u.user_id)
                                             LEFT JOIN users_visits v ON u.user_id = v.visit_id
                                             WHERE f1.friend1 = ".$userid." AND v.visit_now_time > '".(SITE_TIME-600)."')
                                             UNION 
                                             (SELECT * FROM friends f1
                                             RIGHT JOIN friends f2 ON (f1.friend1 = f2.friend2 AND f2.friend1 = f1.friend2)
                                             LEFT JOIN users u ON (f1.friend1 = u.user_id OR f2.friend1 = u.user_id)
                                             LEFT JOIN users_visits v ON u.user_id= v.visit_id
                                             WHERE f2.friend2 = ".$userid." AND v.visit_now_time > '".(SITE_TIME-600)."')                                  
                                            );