用户被提及但未回应的主题

社区 数据与报告
,
1

我连一点SQL都不懂……但有没有可能写一个查询，显示我已被提及但尚未在提及之后回复的主题……（我猜这部分比较棘手，甚至可能无法实现）？

2

用户被提及后尚未回复的话题

-- [params]
-- user_id :user

WITH mentions AS (
    SELECT target_topic_id, target_post_id, created_at
    FROM user_actions
    WHERE action_type = 7 -- 提及
    AND user_id = :user
), replies AS (
    SELECT topic_id, MAX(created_at) created_at
    FROM posts
    WHERE user_id = :user
    AND deleted_at IS NULL
    AND post_type IN (1, 4) -- 普通回复 或 悄悄话
    GROUP BY topic_id
)
SELECT DATE(m.created_at) 被提及时间，target_post_id 帖子 ID
FROM mentions m
LEFT JOIN replies r ON r.topic_id = m.target_topic_id
JOIN topics t ON t.id = m.target_topic_id
WHERE m.created_at > COALESCE(r.created_at, '1900-01-01')
AND t.deleted_at IS NULL
AND NOT t.archived
AND NOT t.closed
ORDER BY m.created_at DESC