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 (
SELECT
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 topics.id = new_topics.new_topic_id