Posts under topics with nested topic_id unincluded

AI-generated summary

uptownyumiko is trying to export Like/Liked interactions by topic using a SQL query but notices that certain topics — specifically those with nested topic_id formats like /t/round-1-introduce-yourself/54/1292 — are missing from results. These topics were originally created last year and later reorganized under a new category this year to reuse titles for new discussion groups. uptownyumiko suspects the nested structure (where the first number is topic_id and second is post_id) may be causing the exclusion and seeks advice on how to retrieve data from such topics. In post 2, they realize the nested format and believe they can now proceed with their search, thanking the community for support. The issue relates to SQL queries and data reporting for nested topic structures in Discourse.

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!

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!