Блог

Главная - Вопросы по программированию - Запрашивает сообщение между двумя сторонами, но скрывает сообщение пользователю, который удалил

Запрашивает сообщение между двумя сторонами, но скрывает сообщение пользователю, который удалил

#mysql #sql

#mysql #sql

Вопрос:

Я создаю систему обмена сообщениями, ниже приведены мои таблицы.

 table --users
          |u_id | Name |
          | 01  | Aku  |
          | 02  | Sika |
          | 03  | Admin|

table --messages 
          |m_id | sender_id | Body   | sender_deleted | msgTime |
          | 100 |  01       | hello  |     Yes        | 16:04   |
          | 200 |  02       | Hi     |     no         | 16:08   |


table --recipient
          |m_id | recipient_id | status | recipient_deleted |
          |100  |   02         | read   |  no               |
          |200  |   01         | unread |  no               |

ПРОБЛЕМА

Я хочу запрашивать диалог из этих таблиц только между двумя сторонами (таким образом u_id=01 and u_id=02 ), опять же, я хочу скрыть сообщения для sender_id, когда sender_deleted=yes но показывать то же сообщение для recipient_id if recipient_deleted = no

ПРИМЕЧАНИЕ-ОБНОВИТЬ

  1. Я хочу, чтобы пользователь с u_id=01 видел только сообщение с m_id=200 при просмотре на ее странице

  2. Я хочу, чтобы пользователь с u_id=02 видел сообщения с m_id=100 и m_id=200 при просмотре на ее странице

Это то, что я пробовал, но я не знаю, как это сделать

 SELECT
      m.sender_id,
      m.Body,
      u.Name,
      u.u_id
FROM 
      messages m
LEFT JOIN
      users u
   ON 
      m.sender_id=u.u_id
LEFT JOIN
      recipient r
   ON
      r.m_id=m.m_id
WHERE
      (m.sender_id=01 OR m.sender_id=02 ) and 
      (r.recipient_id=01 OR r.recipient_id=02)

Комментарии:

1. Как должен выглядеть результирующий набор

2. Пожалуйста, я обновил свой вопрос, надеюсь, это прояснит

Ответ №1:

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

 SELECT m.sender_id, m.recipient_id, m.Body, u_sender.Name as Sender,
       u_recipient.Name as Recipient, sender_deleted, recipient_deleted
FROM messages m
JOIN recipient r ON (m.m_id=r.m_id)
JOIN user u_sender ON (m.sender_id=u_sender.u_id)
JOIN user u_recipient ON (r.recipient_id=u_recipient.u_id)
WHERE 
     m.sender_id IN (01,02) 
 AND 
     r.recipient_id ON (01,02)
 AND
     ((m.sender_id=<id> AND m.sender_deleted='no') 
       OR 
     (r.recipient_id=<id> AND r.recipient_deleted='no'))

Вы должны заменить <id> идентификатор зарегистрированного пользователя (01 или 02)

Вы можете, конечно, изменить возвращаемые поля, чтобы отобразить то, что вам нужно.

Надеюсь, это поможет!

Комментарии:

1. Да, спасибо @Mariano .. это сработало, но не повлияет ли это на производительность при большом количестве строк?

2. Привет @George. Я думаю, что этот запрос будет выполняться хорошо, если установлены правильные индексы. Например, users.u_id должен быть первичный ключ. messages должно иметь m_id значение PK. Для recipient значение PK зависит от того, можете ли вы отправить сообщение нескольким получателям. В этом случае PK для получателей должен быть (m_id,recipient_id) Вы можете создать индексы для sender_deleted / recipient_deleted, чтобы увеличить время поиска. Обратите внимание, что, не используя левые (внешние) соединения, движку приходится намного меньше проходить. Как всегда, MySQL EXPLAIN может вам в этом очень помочь, вы можете поиграть и посмотреть, что получится.

3. Спасибо @Mariano, я понял каждый указанный пункт, и я уже использую тот же процесс, о котором вы упомянули. Но, исходя из вашего последнего пункта, вы имеете в виду, что лучше запретить использование левых (внешних) объединений? это в моем случае выше

4. Да, я думаю, что лучше избегать их. ВНЕШНИЕ соединения полезны, но у них есть по крайней мере 2 недостатка: 1) они не так эффективны, как внутренние соединения, потому что движку приходится приводить записи, которые совпадают, а также записи, которые не совпадают, и 2) легко установить условия таким образом, чтобы привести больше записей, чем вы действительно намереваетесь. Вы должны быть очень осторожны при настройке условий ON / WHERE для внешних соединений. Если это не очень очевидно (т. Е. Используя левое соединение, чтобы избежать использования NOT IN), я стараюсь использовать внутренние.

Ответ №2:

Следующий запрос возвращает столбцы, размещенные в вопросе для требуемых сообщений:

 SELECT
msg_details.sender_id,
msg_details.Body,
u.Name,
u.u_id
FROM users u
INNER JOIN messages msg_details ON u.u_id = msg_details.sender_id
LEFT JOIN messages m ON msg_details.m_id = m.m_id
LEFT JOIN recipient r ON msg_details.m_id = r.m_id
WHERE (m.sender_id = <user_id> AND m.sender_deleted = 'no')
OR (r.recipient_id = <user_id> AND r.recipient_deleted = 'no');

Замените user_id на текущее user_id .

SQL Fiddle demo