0

first of all sorry for my bad english. The scenario i have is the following:

I am developing a notification services that sends notifications messages to many users. I have the following 3 tables on MySql

users(user_id)

notifications(notification_id, notification)

notifications_log(notification_log_id, notification_id, user_id)

Every time that a user read a notification, i insert a record on notifications_log table, ex. John user with user_id = 2 read the notification with notification_id =3: "This is a notification", and then i insert a record on notifications_log with user_id = 2 and notification_id = 3.

There is all ok, but i have to create a query to get all the notifications for all the users that not are inserted on notifications_log. What i have is:

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 */

If there is no records on notifications_log table of user 1, query results show me

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

But if i insert at least 1 record on notifications_log for user and notification_2, then i get empty results, and i should get:

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

It seems that the query joins the notification_log_id to the other record with null notification_log_id...

In short, what I need it is get all the notifications from a especific user that there are not on inserted on the table notifications_log

Thanks in advance!

pala_
  • 8,901
  • 1
  • 15
  • 32
Agustin Nicolas Busso
  • 67
  • 8

2 Answers2

4

The query you want is probably this one:

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

demo here

This query eliminates your derived table, reducing the execution time, and performs the cross join as early as possible to reduce the total number of rows being operating on.

But i'd suggest rethinking this altogether. Once notifications and users table reaches critical mass this is going to create millions upon millions of rows to filter.

A better idea would be to have a notification_inbox table, as a counterpart to your notifications_log table. When a notification is created, place it in the inbox table for each user. That way you can perform a simple query on a single table to determine unread notifications per user, rather than a potentially horrendously performing cross join.

Alternatively again, a single notification_delivery table, rather than inbox and log tables, which has a 'read' flag. This would also allow targeted notifications, as well as bulk delivery to all users.

pala_
  • 8,901
  • 1
  • 15
  • 32
  • The execution time is something that worried me, but with this its seem will be fast. Your code work perfectly, thank you a lot! :) – Agustin Nicolas Busso May 21 '15 at 02:39
  • @AgustinNicolasBusso It will get exponentially worse as your table sizes increase. if There are 1000 users, with 1000 notifications, you get 1,000,000 rows to process. It might not be an issue now, but later on... depends how much use this system gets :) however if it meets your requirements could you please mark it as accepted? – pala_ May 21 '15 at 02:41
  • you are right, i will handle the notifications with a batch process that delete oldest (because business model allow me to do that). Thanks friend! – Agustin Nicolas Busso May 21 '15 at 02:46
  • @AgustinNicolasBusso sounds like a decent idea - if you're happy with this answer could you please mark it as accepted, so it can potentially help others? cheers – pala_ May 21 '15 at 04:22
1

It seems like you are on the right track but should just be changing user_id to notification_id in the 2nd to last line:

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 */
steve klein
  • 2,566
  • 1
  • 14
  • 27