# User-posted code blocks in a category
**URL:** https://meta.discourse.org/t/user-posted-code-blocks-in-a-category/307712
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [May 13, 2024, 2:50am UTC](https://meta.discourse.org/t/user-posted-code-blocks-in-a-category/307712 "2024-05-13T02:50:37Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@jordan-violet](https://meta.discourse.org/u/jordan-violet)
#### Post date: [May 13, 2024, 2:50am UTC](https://meta.discourse.org/t/user-posted-code-blocks-in-a-category/307712/1 "2024-05-13T02:50:37Z")
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.
````sql
-- [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 '%```%'
````
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [May 13, 2024, 6:17am UTC](https://meta.discourse.org/t/user-posted-code-blocks-in-a-category/307712/2 "2024-05-13T06:17:38Z")
I’m not sure if you’re exporting this data, but if you’re using it within the [data explorer](https://meta.discourse.org/t/32566?silent=true) 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:
````sql
-- [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:
> [@Search for keywords across posts](https://meta.discourse.org/t/search-for-keywords-across-posts/134553):
>
> This query will let you efficiently search for a keyword across all posts. It will be significantly faster than a posts.raw LIKE query, because it uses the postgres [full text search](https://www.postgresql.org/docs/9.5/textsearch.html) structured data -- [params] -- string :query SELECT p.id as post\_id FROM posts p LEFT JOIN post\_search\_data psd ON psd.post\_id = p.id WHERE psd.search\_data @@ TO\_TSQUERY(:query)
---
### Author:  [@jordan-violet](https://meta.discourse.org/u/jordan-violet)
#### Post date: [May 13, 2024, 11:19am UTC](https://meta.discourse.org/t/user-posted-code-blocks-in-a-category/307712/3 "2024-05-13T11:19:33Z")
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.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [May 13, 2024, 5:58pm UTC](https://meta.discourse.org/t/user-posted-code-blocks-in-a-category/307712/4 "2024-05-13T17:58:49Z")
You can also extend it to handle a user list version as well if you want to have multiple. eg:
```sql
-- [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 - [Using Parameters in Data Explorer Queries](https://meta.discourse.org/t/utilizing-parameters-in-data-explorer-queries/277934)
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](https://meta.discourse.org/t/common-discourse-data-tips/276148))