MySql CROSS СОЕДИНЕНИЕ между двумя таблицами и сопоставление с другим

Question

Прежде всего извините за мой плохой английский. У меня есть следующий сценарий:

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

users(user_id)

notifications(notification_id, notification)

notifications_log(notification_log_id, notification_id, user_id)

Каждый раз, когда пользователь читает уведомление, я вставляю запись в таблицу notifications_log, напр. Джон пользователь с user_id = 2 прочитайте уведомление с notification_id =3: "Это уведомление", а затем я вставляю запись в notifications_loг с user_id = 2 а также notification_id = 3,

Там все в порядке, но мне нужно создать запрос, чтобы получить все уведомления для всех пользователей, которые не вставлены в notifications_log. Что у меня есть:

SELECT u.user_id, n.notification_id, n.notification, nl.notification_log_id
FROM users as u
LEFT JOIN notifications_log as nl ON nl.user_id = u.user_id
CROSS JOIN notifications as n
WHERE u.user_id NOT IN (SELECT nl.user_id FROM notifications_log as nl)
AND u.user_id = 1 /* test with user 1 */

Если в таблице notifications_log пользователя 1 нет записей, результаты запроса показывают

user_id   |   notification        |   notification_id   |  notification_log_id
------------------------------------------------------------------------------    
 - 1      |   Notification_1      |   1                 |  null  
 - 1      |   Notification_2      |   2                 |  null

Но если я вставлю хотя бы 1 запись на notifications_log для пользователя и Notification_2, тогда я получаю пустые результаты, и я должен получить:

user_id   |   notification      |   notification_id   |  notification_log_id
----------------------------------------------------------------------------    
 - 1      |   Notification_1    |   1                 |  null  

Похоже, что запрос соединяет компонент messages_log_id с другой записью с нулевым значением параметра messages_log_id...

Короче говоря, мне нужно получить все уведомления от конкретного пользователя, которые не включены в таблицу notifications_log.

Заранее спасибо!

Answer 1

Возможно, вам нужен следующий запрос:

select n.notification_id, u.user_id
  from notifications n
    cross join users u
    left join notifications_log nl
      on n.notification_id = nl.notification_id
        and nl.user_id = u.user_id
   where nl.notification_log_id is null

демо здесь

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

Но я бы предложил переосмыслить это вообще. Как только таблица уведомлений и пользователей достигнет критической массы, это создаст миллионы и миллионы строк для фильтрации.

Лучшей идеей было бы иметь таблицу messages_inbox в качестве аналога вашей таблицы notifications_log. После создания уведомления поместите его в таблицу входящих сообщений для каждого пользователя. Таким образом, вы можете выполнить простой запрос к одной таблице для определения непрочитанных уведомлений для пользователя, а не для потенциально ужасных действий cross join,

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

Answer 2

Похоже, что вы на правильном пути, но вам нужно просто изменить user_id на Notification_id во 2-й до последней строки:

SELECT u.user_id, n.notification_id, n.notification, nl.notification_log_id
FROM users as u
LEFT JOIN notifications_log as nl ON nl.user_id = u.user_id
CROSS JOIN notifications as n
WHERE n.notification_id NOT IN (SELECT nl.notification_id FROM notifications_log as nl)
AND u.user_id = 1 /* test with user 1 */