Find users who read specific topics

Community Building Data & reporting
11

That’s right. You can alter the query so that it doesn’t only select users who have posted by removing this part:

AND tu.posted = true

Try this:

-- [params]
-- int :topic_id

WITH user_data AS (
  SELECT u.username,
    ue.email,
    tu.topic_id,
    tu.posted,
    tu.liked
    FROM users u
    JOIN topic_users tu ON u.id = tu.user_id 
    JOIN user_emails ue on u.id = ue.user_id
    ORDER BY tu.last_visited_at DESC
)
SELECT * FROM user_data WHERE topic_id = :topic_id