# Badges for posting
**URL:** https://meta.discourse.org/t/badges-for-posting/276725
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [August 19, 2014, 10:12pm UTC](https://meta.discourse.org/t/badges-for-posting/276725 "2014-08-19T22:12:41Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@PJH](https://meta.discourse.org/u/PJH)
#### Post date: [August 19, 2014, 10:12pm UTC](https://meta.discourse.org/t/badges-for-posting/276725/1 "2014-08-19T22:12:41Z")
Since there deliberately isn’t the concept of achievements for posting, I’ve managed to implement it on ours, since we had it on our old forums and some were complain about the lack thereof.
Generic version:
```mysql
SELECT user_id, 0 post_id, current_timestamp granted_at
FROM badge_posts
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id
HAVING count(*) > 1000
```
Change the 1000 for whatever post limit you’re after. I’ve themed ours (due to the nature of our board) on powers of 2.
Note that this will (should) _ **only** _ count posts that are public (not in categories that are restricted or private messages) or otherwise unrestricted (checkbox on edit category ignoring badges)
Additionally, for our board I’ve had to exclude certain topics due to the nature of my charges:
```mysql
SELECT user_id, 0 post_id, current_timestamp granted_at
FROM badge_posts
WHERE topic_id NOT IN (
[elided]
) AND topic_id NOT IN (
[elided]
) AND (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id
HAVING count(*) >= 2048
```
Edit: There was a problem with my queries - `post_id` is apparently required when this is triggered on post-related triggers.
See
[https://meta.discourse.org/t/badges-sql-problem/19295](https://meta.discourse.org/t/badges-sql-problem/19295)
And
> [@Creating triggered custom badge queries](https://meta.discourse.org/t/triggered-custom-badge-queries/19336):
>
> bookmark This guide explains how to create triggered custom badge queries in Discourse, including the types of badges, constraints for triggered badges, and an example query. person_raising_hand Required user level: Administrator warning This feature is disabled by default. To enable it, follow [this guide](https://meta.discourse.org/t/badge-sql-can-no-longer-be-edited-by-default/47894). When defining badges in Discourse, you’ll encounter a “Trigger” option with the following choices: Update daily When a user acts on post When a user edits or creates a post When…
Edit 2: Added `:backfill`
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [September 3, 2014, 3:51am UTC](https://meta.discourse.org/t/badges-for-posting/276725/2 "2014-09-03T03:51:58Z")
I think I have the correct trigger condition here, actually:
```plaintext
:backfill OR
user_id IN (
SELECT trigger_post.user_id FROM posts trigger_post WHERE trigger_post.id IN (:post_ids)
)
```
You get the user id list from the list of triggered posts and filter on that.
---
### Author:  [@ckshen](https://meta.discourse.org/u/ckshen)
#### Post date: [July 20, 2016, 6:50am UTC](https://meta.discourse.org/t/badges-for-posting/276725/3 "2016-07-20T06:50:56Z")
> [@PJH](#):
>
> Since there deliberately isn’t the concept of achievements for posting, I’ve managed to implement it on ours, since we had it on our old forums and some were complain about the lack thereof.
>
> Generic version:
>
> SELECT user\_id, 0 post\_id, current\_timestamp granted\_at
> FROM badge\_posts
> WHERE (:backfill OR user\_id IN (:user\_ids) OR 0 NOT IN (:post\_ids) )
> GROUP BY user\_id
> HAVING count(\*) \> 1000
Thanks. This is very helpful. I am also thinking about encouraging users to initiate discussions. I am messing around your query to count original posts with p.post\_number = 1
---
### Author:  [@AntiMetaman](https://meta.discourse.org/u/AntiMetaman)
#### Post date: [August 1, 2020, 6:06am UTC](https://meta.discourse.org/t/badges-for-posting/276725/4 "2020-08-01T06:06:35Z")
> [@PJH](#):
>
> ```plaintext
> SELECT user_id, 0 post_id, current_timestamp granted_at
> FROM badge_posts
> WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
> GROUP BY user_id
> HAVING count(*) > 1000
>
> ```
I just found this little gem here. I wanted to create my own custom badge queries for number of posts in specific categories. How can one modify this to also include the condition of specific category ids so only posts in a specific category will count?
> [@Some common badge queries](https://meta.discourse.org/t/some-common-badge-queries-idea/31859):
>
> warning Enabling Badge SQL entails security and performance risks so it is not available by default. For more information, see: [Enable Badge SQL](https://meta.discourse.org/t/badge-sql-can-no-longer-be-edited-by-default/47894) Here are some common badge queries which you can use to create your own awesome badge! Grant a badge if user has created at least one topic in the “foo”\* category \*(“foo” is the category name, not the category slug) SELECT p.user\_id, min(p.created\_at) granted\_at, MIN(p.id) post\_id FROM badge\_posts p JOIN topics t ON t.id = p.topic\_id WHERE cate…
Should be a modification of the first query here where badges are awarded based on creating topics in a specific category. The difference is we award based on number of posts in a specific category.
---
### Author:  [@ckshen](https://meta.discourse.org/u/ckshen)
#### Post date: [August 27, 2024, 12:41am UTC](https://meta.discourse.org/t/badges-for-posting/276725/5 "2024-08-27T00:41:41Z")
> [@PJH](#):
>
> Generic version:
>
> ```plaintext
> SELECT user_id, 0 post_id, current_timestamp granted_at
> FROM badge_posts
> WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
> GROUP BY user_id
> HAVING count(*) > 1000
>
> ```
Runs into this error when running the badge query preview:
```plaintext
There was an error with the query. Any idea how to fix it?
Contract violation:
Query triggers on posts, but references the ':user_ids' array
```