People receiving reminders even though they've accepted the policy

Product Support
9

Just to follow up here publically for future travellers. :slight_smile:

I think something like this would allow you to check out the relevant information and see if a notification was sent after the policy had been accepted:

-- [params]
-- topic_id :topic_id

SELECT pp.id AS "Policy ID",
       pp.created_at AS "Policy Created",
       n.user_id,
       n.created_at AS "Last Notification Received",
       pu.accepted_at AS "User Accepted Policy",
       pu.revoked_at AS "User Revoked Policy",
       pu.expired_at AS "Policy Expired"
FROM notifications n
JOIN posts p ON p.topic_id = n.topic_id 
JOIN post_policies pp ON pp.post_id = p.id
LEFT JOIN policy_users pu ON pu.post_policy_id = pp.id AND pu.user_id = n.user_id
WHERE n.topic_id = :topic_id
  AND n.user_id > 0
ORDER BY n.user_id, pu.accepted_at