We sometimes have requirements from other internal teams to search for certain information. This query came in handy for us to find what we needed.
-- [params]
-- user_id :user
-- int_list :category_ids = 0
SELECT
p.id AS post_id,
p.raw,
p.created_at,
p.topic_id
FROM posts p
WHERE p.user_id = :user
AND p.topic_id IN (
SELECT t.id
FROM topics t
WHERE (':category_ids' = 0 OR t.category_id IN (:category_ids))
AND t.archetype <> 'private_message'
)
AND p.raw LIKE '%```%'
I’m not sure if you’re exporting this data, but if you’re using it within the data explorer one of the nifty tricks I love to lean on for all user-based lookups is the user_id parameter which gives you a super easy look-up input box:
-- [params]
-- user_id :user
-- int_list :category_ids = 0
SELECT
p.id AS post_id,
p.raw,
p.created_at,
p.topic_id
FROM posts p
WHERE p.user_id = :user
AND p.topic_id IN (
SELECT t.id
FROM topics t
WHERE (':category_ids' = 0 OR t.category_id IN (:category_ids))
AND t.archetype <> 'private_message'
)
AND p.raw LIKE '%```%'
This variation also includes the AS post_id magic to turn the p.id into a usable link in the onscreen results, as well as expanding the category parameter to allow for all or multiple categories as well (0 for all, or a comma-separated list for multiple. eg. 4, 5, 6).
This one seems relatively speedy, but there’s also this tip for an alternative method for searching keywords too:
I’ve updated my original query with this! I actually used Discourse AI’s SQL builder to help me accomplish this. It has actually been an incredible experience that has saved me probably 4-5 hours every single week.
There is a category_id and group_id parameter though they’re not as swish as the user_id one where you get a selection box (they work by typing in the name and it magically converts it to the id). They can be quite useful still, though.
(Also, another generally useful bit of info that can help refine some of the AI-assisted queries is Common Discourse data tips)