显示读取而不是readed通知

Question

I'm developing a custom notifications system with PHP and MariaDB. I'm struggling with the "read notification" logic.

This is my notifications table:

|id | title       | content       | uid        | visibility | timestamp |
+------------------------------------------------------------------------+
|11 | Other title | Other content | sz46j86rd  | public     | 0123456789 |
|10 | My title    | My content    | xrgoyyzm27 | private    | 0123456789 |
... And so on ...

And this is my notifications_viewed table:

| id | user_id | notification_uid | timestamp  |
+-----------------------------------------------+
| 2  | 1       | xrgoyyzm27       | 1234567890 |

So, what I need is retrieve from the database the notifications and check if that notification has been viewed/readed by the current user.

I'm already showing the notifications (that works just fine) but I don't know how to check if each of the last 5 notifications that I show in the topbar dropdown has been seen/view/read by the current user. Maybe some kinf of SQL join?

Answer 1

Use LEFT JOIN

SELECT n.*, IF(nv.id IS NULL, 'No', 'Yes') AS viewed
FROM notifications AS n
LEFT JOIN notifications_viewed AS nv ON n.uid = nv.notification_uid AND nv.user_id = :current_user

BTW, foreign keys usually refer to the primary key of the parent table.