User-posted code blocks in a category

data & reporting
1

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 '%```%'
5 Likes
2

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:

4 Likes
3
  1. That user id selector is amazing!
  2. Is there a selector for category as well?

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.

3 Likes
4

You can also extend it to handle a user list version as well if you want to have multiple. eg:

-- [params]
-- user_list :users

SELECT 
    id AS user_id,
    created_at 
FROM users 
WHERE id IN (:users)

There’s a bit more info about some of the other magic parameters here - Utilizing Parameters in Data Explorer Queries

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)

1 Like