CODEWITHSUNDEEP
mysqlsql
Vani Kalapciev
Vani Kalapciev

Reputation: 82

Read notifications table

I am making a notifications table. That is structured like this:

notifications:    
+----+------+----------------------+-----------------------------------+-----------+
| ID | user | notification         | message                           | timestamp |
+----+------+----------------------+-----------------------------------+-----------+
| 1  | 6    | Denied acceptance    | You have been denied for...       |           |
| 2  | 6    | Apreooved acceptance | You have been accepted...         |           |
| 3  | 0    | Open slots           | there are still open slots for... |           |
+----+------+----------------------+-----------------------------------+-----------+

And a table that indicates which notification was read by each user

notifications_read    
+----+------+--------------+-----------+
| ID | user | notification | timestamp |
+----+------+--------------+-----------+
| 1  | 6    | 2            |           |
+----+------+--------------+-----------+

If notification is meant only for one user, there is a user id under "user" in "notifications"- If the notification is meant for everybody I insert '0' under user.

I am trying to make a query that selects rows from "notifications", that is not read.

My current query is not ok:

SELECT * 
FROM notifications 
WHERE id NOT IN 
    (SELECT notification 
     FROM notification_read 
     WHERE user = $id) AND user = $id OR user = 0

So for above table it needs to select the rows with id 1 & 3

Thank you for the help!

Upvotes: 0

Views: 507

Answers (2)

Ryan Tuosto
Ryan Tuosto

Reputation: 1951

SELECT n.*
FROM notifications n
LEFT JOIN notifications_read nr ON nr.notification = n.id
WHERE
nr.id IS NULL

This will give you notifications that don't exist n the notifications_read table.

Left join will give you all rows in notifications and join rows from notifications_read that have a notification id. So when you filter by nr.id IS NULL you will return rows that exist in notifications but not in notifications_read.

Additionally, the LEFT JOIN will perform better.

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270693

If you want unread notifications for a particular user, then you are on the right track:

SELECT n.* 
FROM notifications n
WHERE n.id NOT IN (SELECT nr.notification 
                   FROM notification_read nr
                   WHERE nr.user = $id) AND
      (n.user = $id OR n.user = 0);

I think the only issue is the parentheses for the logic around OR.

You can also write this using IN:

SELECT n.* 
FROM notifications n
WHERE n.id NOT IN (SELECT nr.notification 
                   FROM notification_read nr
                   WHERE nr.user = $id) AND
      n.user IN (0, $id);

Upvotes: 1

Related Questions