Взаимные друзья - MySQL

Question

Я пытаюсь разработать функцию, которая показывает мне друзей друзей и сколько у меня общих друзей с этими пользователями. До сих пор я мог выполнять функции отдельно, но не вместе.

Вот основной вид, который я использую под названием userfriends. Где user1 - первый пользователь, а user2 - друг.

Это функция, которую я разработал, чтобы видеть общих друзей между двумя пользователями

SELECT id FROM users
WHERE id IN (
    SELECT user2 FROM userfriends WHERE user1 = me
) AND id IN (
    SELECT user2 FROM userfriends WHERE user1 = second
)

Users - это основная таблица, которая может связать идентификатор пользователя, найденный в таблице userfriends, с информацией о пользователе. Me и second являются переменными в хранимой процедуре, которые эмулируют поиск первого и второго пользователей. Эта функция работает по плану.

Вторая функция, которую я имею, состоит в том, чтобы видеть всех пользователей, которые дружат с моими друзьями, но не со мной.

SELECT user2 AS id
FROM userfriends
    WHERE user1 IN (
        #Selects my friends
        SELECT user2 FROM userfriends
        WHERE user1 = me
    ) 
    AND user2 <> me #Makes sure is not me
    AND user2 NOT IN ( #Makes sure not already friend
        SELECT user2 FROM userfriends
        WHERE user1 = me
    )

Опять же, все работает по плану, и я представляю идентификатор пользователя. Это вернет список всех моих друзей, друзей.

То, что я хочу получить вместо списка общих пользователей или списка друзей-друзей, это:

Таблица, в которой есть идентификатор пользователя моего друга и сколько общих друзей меня и этого пользователя. Etc: user: 1, friends_in_common: 103. Если я не достаточно ясен, пожалуйста, спросите, и я постараюсь прояснить ситуацию. Две функции делают это самостоятельно, но я просто не уверен, как объединить это вместе.

Answer 1

-- use a self-join of userfriend*userfriend
-- to find all the friends that moi&toi have in common
-- , and count them.
-- (optionally) suppress the rows for (toi<=moi)
-- , because there are basically the same as the corresponding rows with
-- (toi>moi)
-- -----------------------------------
SELECT DISTINCT 
   uf1.user1 AS moi
   , uf2.user1 AS toi
   , COUNT(*) AS cnt
   FROM userfriend uf1
   JOIN userfriend uf2 ON uf1.user2 = uf2.user2
   WHERE uf1.user1 < uf2.user1 -- Tie breaker, symmetry remover
   GROUP BY uf1.user1, uf2.user1
   ;