Read history for a particular category

[Cozdabuch (cosmo)]

@vinothkannans

SELECT tu.topic_id, tu.last_read_post_number FROM topics LEFT OUTER JOIN topic_users AS tu ON topics.id = tu.topic_id WHERE topics.category_id = 4 AND tu.user_id = 1 AND tu.last_visited_at IS NOT NULL

Is there a way to write this specific query:

• For topics in category (category id: 34) viewed by user(s) not in group (executive_board)
  • Display user, display topic, display when viewed.

[Cozdabuch (cosmo)]

신기하게도 이 게시물이 자동으로 올라왔네요. 여전히 충분히 관련성이 있어서 누군가 답을 주셨으면 좋겠습니다.

[JammyDodger]

네, 이걸 꽤 오래 기다리셨네요.

아래와 같은 방식으로 처리할 수 있을 것 같습니다. (executive_board의 group_id를 찾아서 group_id = 3으로 교체해야 합니다.)

SELECT tu.user_id,
       tu.topic_id, 
       tu.last_read_post_number,
       tu.last_visited_at AS read
FROM topics t
LEFT OUTER JOIN topic_users tu ON t.id = tu.topic_id 
WHERE tu.user_id NOT IN (SELECT user_id 
    FROM group_users
    WHERE group_id = 3)
AND t.category_id = 34
AND tu.last_visited_at IS NOT NULL
ORDER BY tu.last_visited_at DESC