Posts under topics with nested topic_id unincluded


(sakura793) #1

Hi,

I am trying to export the list of Like and Liked interaction by topics remixing the sample code, and realized that the topics I was particularly trying to look at seemed to not be included in the list. The code I tried was:

WITH pairs AS (
SELECT p.user_id liked, pa.user_id liker, p.topic_id topic
FROM post_actions pa
LEFT JOIN posts p ON p.id = pa.post_id
WHERE post_action_type_id = 2 --2 means liked
)
SELECT liker liker_user_id, liked liked_user_id, topic topic_id, count(*)
FROM pairs
GROUP BY liked, liker, topic_id
ORDER BY topic_id DESC

I’ve noticed that the difference between the topics I am looking for and the topics I get is that the former’s topic_id is nested: example one and two. These topics with nested topic_id were created last year, reorganized as a new category early this year, because we needed to create topics with the same titles to run the same set of discussion with a new group of people.

Please let me know if anyone know how I should find those, or any ideas to retrieve data from those topics with nested topic_id.

I really appreciate your help in advance!


(sakura793) #2

I figured the first number is the topic number and the second number is the post number…

Looks like I can proceed with the search. Thanks!