Mysql Query Help для рекомендуемых друзей

Question

Mysql Query Help для рекомендуемых друзей

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

Вот запрос

SELECT 
  IF(Friends.User_Id1 IN (1111,2222),
       Friends.User_Id2,
       Friends.User_Id1) AS 'Friend_Id', 
  CONCAT(User_FirstName," ",User_LastName) AS User_FirstName,
  User_ProfilePic 
FROM Users
JOIN Friends ON
  IF(Friends.User_Id1 IN (1111,2222),
       Friends.User_Id2,
       Friends.User_Id1) = Users.User_Id
WHERE 
 (Friends.User_Id2 IN (1111,2222) OR Friends.User_Id1 IN (1111,2222)) AND 
 (Friends.User_Id2 != MY_ID AND Friends.User_Id1 != MY_ID AND Friends.Status = 1)
LIMIT 10;

В приведенном выше случае, 1111 и 2222 - мои друзья, и я пытаюсь получить всех своих друзей, это хорошо, но я хочу:

пользователи уже мои друзья, которые также дружат с 1111 и 2222 и отображаются в списке. Я не хочу их здесь, потому что они уже в другом списке друзей.
Пользователи, которых я заблокировал, т.е. Friends.Status для MY_ID и friends_friend_id = 3, у меня тоже есть один, в этом случае, идентификатор пользователя 3333 является другом 2222, и я уже заблокировал его, но он присутствует в списке.

Пожалуйста, направьте меня, если поиск через IN(1111,2222) может привести к некоторой проблеме в будущем, потому что количество друзей определенно увеличится. У меня есть список моих друзей через запятую group_concat до вышеуказанного запроса. Все это в хранимой процедуре.

Я надеюсь, что я объяснил проблему ясно.

0

sql mysql join sql-subselect

Источник

user806440 05 мар '12 в 15:48

2 ответа

Решение

Как и в ответе Майка, и я тоже, ваша структура таблицы

create table Friends (
   user_Id1 int,
   user_Id2 int,
   status int);

То, что вы, похоже, хотите, - это ОТЛИЧНЫЙ список друзей, которые являются либо вашими друзьями, либо друзьями, НАПРАВЛЕННЫМИ СВЯЗАННЫМИ с вашими друзьями (т.е. 1 степень разлуки с вами). Итак, давайте идти по этому сценарию.

У вас есть личность ID 1111 и у вас есть друзья 2222 и 3333.

У человека 2222 есть друзья 1111 (вы), 3333 (ваш другой друг) и 4444 (новый человек).

У человека 3333 есть друзья 1111 (вы), 4444 (такой же, как у человека 3333 - совпадение) и 5555.

Теперь, 2-я степень разделения (не то, что вы ищете) - это то, что у человека 4444 есть друг 6666, 7777, 8888. Вам не безразличны эти другие (6666, 7777, 8888)

Вы ищете весь список друзей, которые не являются вами, и просто хотите увидеть друзей 2222, 3333, 4444, 5555.

Я хотел бы начать со списка только ваших друзей и использовать его в качестве основы, чтобы получить их друзей. Самый внутренний запрос - просто найти своих отличных друзей (без жесткого кодирования того, кто ваши друзья). Тогда от того, что получить всех своих друзей. Если они окажутся похожими, "DISTINCT" отфильтрует это для вас. После того, как они выбраны, получите Союз ваших прямых друзей, чтобы представлять "AllFriends". Используя IF(), мы хотим, чтобы любой "другой" человек основывался на квалификации объединения. Если присоединяемый человек находится в положении 1, тогда мы хотим ДРУГОГО человека, и наоборот. После того, как у вас есть отдельный список друзей, ТОГДА присоедините его к таблице пользователей для получения их имени, изображения и любой другой информации профиля.

select
      AllFriends.FinalFriendID,
      CONCAT(U.User_FirstName, " ", U.User_LastName) AS User_FirstName, 
      U.User_ProfilePic 
   from
       ( select DISTINCT
               IF( F2.User_ID1 = YourDirectFriends.PrimaryFriend, F2.User_ID2, F2.User_ID1 ) 
                  as FinalFriendID
            from
               ( select DISTINCT 
                        IF( F.User_ID1 = YourID, F.User_ID2, F.User_ID1 ) as PrimaryFriendID
                   from
                      Friends F
                   where
                         F.user_ID1 = YourID
                      OR F.User_ID2 = YourID ) YourDirectFriends
                JOIN Friends F2
                   ON    YourDirectFriends.PrimaryFriendID = F2.User_ID1
                      OR YourDirectFriends.PrimaryFriendID = F2.User_ID2
         UNION 
         select DISTINCT
               IF( F.User_ID1 = YourID, F.User_ID2, F.User_ID1 ) as FinalFriendID
            from
               Friends F
            where
                  F.user_ID1 = YourID
               OR F.User_ID2 = YourID ) ) as AllFriends
        JOIN Users U
           on AllFriends.FinalFriendID = U.User_ID

О, да, добавьте в свой квалификатор "Статус", где это применимо, и ваше предельное требование.

1

Источник

user74195 05 мар '12 в 17:46

Другие вопросы по тегам sql mysql join sql-subselect

user341547 05 мар '12 в 17:08 2012-03-05 17:08 · Accepted Answer · 2012-03-05 17:08

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

Как я понимаю вашу схему, это:

create table Friends (
   user_Id1 int,
   user_Id2 int,
   status int);

Если в любое время есть дружеские отношения, один из идентификаторов находится в позиции 1, а 1 - в позиции 2.

Теперь, предполагая, что мой идентификатор 1212, мой список идентификаторов друга:

 select user_Id1 as my_friends_userId
   from Friends f
   where f.user_Id2 = '1212'
      and status = 1
 union
 select user_Id2 as my_friends_userId
   from Friends f
   where f.user_Id1 = '1212'
     and status = 1;

Список идентификаторов друзей моих друзей:

select f1.user_id1 as friends_of_friends
  from Friends f1
  where f1.user_Id2 in (select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1)
union
select user_id2 as friends_of_friends
  from Friends f1
  where f1.user_Id1 in (
      select user_Id1 as my_friends_userId
        from Friends f
        where f.user_Id2 = '1212'
          and status = 1
      union
      select user_Id1 as my_friends_userId
        from Friends f
        where f.user_Id1 = '1212'
          and status = 1);

И затем, чтобы добавить исключения для моих собственных друзей и друзей, которых я заблокировал, это становится:

select f1.user_id1 as friends_of_friends
from Friends f1
where f1.user_Id2 in (select user_Id1 as my_friends_userId  /* sub-query for friends of friends */
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1)
and f1.user_id1 not in   /* exclusion of my own friends */
(select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1
 )
and f1.user_id1 != '1212'  /* exclusion of myself. */
and f1.user_id1 not in (select user_Id1 as my_friends_userId  /* exlusion of people I've blocked. */
      from Friends f
      where f.user_Id2 = '1212'
        and status = 3
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 3
 )
union  /* Now do it all over again for user_id2 */
select f2.user_id2 as friends_of_friends
from Friends f2
where f2.user_Id1 in (select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1)
and f2.user_id2 not in 
(select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 1
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 1
 )
and f2.user_id2 != '1212'
and f2.user_id2 not in (select user_Id1 as my_friends_userId
      from Friends f
      where f.user_Id2 = '1212'
        and status = 3
      union
      select user_Id2 as my_friends_userId
      from Friends f
      where f.user_Id1 = '1212'
        and status = 3
 )

где я отметил первый раз для каждого из этих условий. Теперь вы можете увидеть беспорядок unionЯ должен был сделать для этого. (Что, вероятно, должно быть union distinct)

Вы не должны создавать предложение с помощью group-concat. Несмотря на длину здесь, это быстрее.

Вы можете спросить, что делают отдельные части. Но опять же, мой совет не делай этого. Вот почему хороший дизайн стола делает вещи намного проще.

SQL Fiddle для справки и для отображения результатов: http://sqlfiddle.com/

РЕДАКТИРОВАТЬ: Просто чтобы добавить о том, как я бы изменить эту схему.

Неясно, являются ли отношения между друзьями в вашем приложении Google (разрешены асимметричные отношения) или Facebook (разрешены только симметричные отношения).

В обоих случаях я бы изменил схему на:

create table Friends (
  individual_userId int,
  friend_userId int,
  status int);

В гугле, дела, все готово. В случае с Facebook я бы использовал эту структуру, но потребовал бы, чтобы для каждого отношения в таблицу входили две строки. Таким образом, если "1212" - это друзья из Facebook с "0415", то есть (индивидуальный_пользователь, идентификатор_пользователя) строк ("1212", "0415") & ("0415", "1212"). Чтобы это работало и поддерживалось, потребуются хранимые процедуры для вставок / удалений, чтобы гарантировать, что обе строки добавлены и удалены. (Там нет обновления - это уникальные идентификаторы.)

Если мы уверены, что эти отношения поддерживаются и что друг, инициирующий отношения, всегда присутствует в индивидуальном_пользователе, тогда мой последний запрос будет выглядеть так:

select f1.friend_userId as friends_of_friends
from Friends f1
where f1.individual_userId in (   /* retrieve my friend list */
      select friend_userId as my_friends_userId
      from Friends f
      where f.individual_userId = '1212'
        and status = 1)
and f1.friend_userId not in (   /* exclusion of my own friends */
      select friend_userId as my_friends_userId
      from Friends f
      where f.individual_userId = '1212'
        and status = 1
 )
and f1.friend_userId not in ( /* exlusion of people I have blocked. */
      select friend_userId as my_friends_userId
      from Friends f
      where f.individual_userId = '1212'
        and status = 3
 )
and f1.friend_userId != '1212'  /* exclusion of myself. */

с которым гораздо проще иметь дело. Вы также можете переписать это как серию объединений, но я подозреваю, что в качестве первого шага, используя in а также not in такие пункты легче читать.

Пересмотренный sqlfiddle: http://sqlfiddle.com/

(Я должен был бы проверить это с большим набором данных, но моя интуиция говорит, что объединения будут быстрее - но для кода, подобного этому, я подозреваю, что изучение / получение правильного ответа важнее, чем первоначальная оптимизация для скорости.)