User-posted code blocks in a category

Data & reporting
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