I have a table, let's just call TableA, that has a relationship with an account table, let's call TableB. Now, TableA is kind of a notification that I track who already read it using a m2m field with TableB. I want to have an API endpoint that I can pass the current logged in user's ID to it and return the notifications(TableA's records) that hasn't been read by that user(TableB). And I can't figure out the right query for this.
Previously, I have something that looks like this:
SELECT server_notifications.id, server_notifications.created_at,
server_notifications.updated_at, server_notifications.message,
snr.account_id, snr.server_notification_id
FROM `server_notifications`
LEFT JOIN server_notif_read_by_accounts snr
ON server_notifications.id = snr.server_notification_id
WHERE (snr.account_id IS NULL OR snr.account_id <> 'some-account-id')
AND `server_notifications`.`deleted_at` IS NULL;
But I realized that it would correctly return what it should return (lol, sigh), but isn't what I expected. The query does return the TableA record that aren't associated to the account id I passed, but what I wanted to get is every TableA record that isn't associated to an account_id yet or is already associated, but shouldn't include the other records associated to a different account_id.
I think I already found the answer to my question. I need to use NOT EXISTS for the query, which I really haven't used. https://stackoverflow.com/a/6545240/10464730