Off-topic posts that deserved their own topic

While working on this

I was looking for a way to find topics that were created as a result of a moderator splitting posts into a new topic. (i.e. off-topic posts that deserved their own topic)

It wasn’t as easy as I had hoped. I looked at various “topic” and “post” tables with no success.

Being sure I could find a lead to what I needed in Admin → Logs → Staff Actions I was disappointed to find that “split” data was not there (not that it should, hopefully it doesn’t happen all that often)

The only place I could find the id of a topic created this way is in “small-action” posts as part of a link.

Getting the id portion of the string to an integer that can be used is a bit involved.

regexp_matches returns a string array
array_to_string converts the array to a string
CAST converts the string to an integer

WITH new_topics AS (
 CAST( array_to_string(regexp_matches(posts.raw, '([\d]+)(?:\))$', 'g'), '') AS integer) AS new_topic_id
 FROM posts 
 WHERE action_code LIKE 'split_topic'
 AND raw LIKE '%posts were split to a new topic%'
SELECT topics.title 
FROM topics, new_topics
WHERE = new_topics.new_topic_id