# Creating triggered custom badge queries

**URL:** https://meta.discourse.org/t/creating-triggered-custom-badge-queries/19336
**Category:** Site Management
**Tags:** badges, how-to, sql-triggered-badge
**Created:** [8월 27, 2014, 8:42오전 UTC](https://meta.discourse.org/t/creating-triggered-custom-badge-queries/19336 "2014-08-27T08:42:47Z")
**Posts on this page:** 1
**Showing post:** 1

<div class="post-metadata">

### Author: ![Discourse](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/discourse/32/148734_2.png) [@Discourse](https://meta.discourse.org/u/Discourse)
#### Post date: [8월 27, 2014, 8:42오전 UTC](https://meta.discourse.org/t/creating-triggered-custom-badge-queries/19336/1 "2014-08-27T08:42:47Z")

</div>

> 🔖 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.
> 
> 🙋 Required user level: Administrator

> ⚠ 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 a user changes trust level
- When a user is edited or created

 ![Badge Trigger Options](https://global.discourse-cdn.com/meta/original/3X/5/c/5ce31c4163e65073cdb0e2649d99648f5c520b53.png)

These triggers force badges to run at intervals more frequent than daily, ensuring users are notified of new badges closer to when the action occurred.

## Types of badges

There are two types of badges you can define:

1. Badges that target posts
2. Badges that do not target posts

All badge SQL definitions require you to select the columns `user_id` and `granted_at`. If your badge targets posts, you must also select a column named `post_id`.

> ℹ If these columns are not directly available, you can alias them. For example: `u.id as user_id`

## Constraints for triggered badges

Since triggered badges can run once a minute, you need to provide more “hinting” in the badge definition. It’s not sufficient to return the full set of badges; you must also provide hints about how to execute the badge on a subset.

### User-based triggers

If your trigger is user-based, supply a clause on how to filter it based on `:user_ids`.

### Post-based triggers

If your trigger is post-based, supply information on how to trigger it based on `:post_ids`.

> ❗ Remember that a full backfill runs daily regardless, so you must consider this and include handling of the `:backfill` parameter.

Your triggered badge query will always include the `:backfill` param and either the `:post_ids` param or `:user_ids` param.

## Example of a triggered badge query

Here’s an example of a badge triggered “when a user acts on a post”. In this case, “delta” applications will receive the `:post_ids` parameter:

```sql
SELECT p.user_id, p.id post_id, p.updated_at granted_at
FROM badge_posts p
WHERE p.like_count >= 25
AND (:backfill OR p.id IN (:post_ids) )

```

The clause `(:backfill OR p.id IN (:post_ids) )` allows filtering of the results. When the daily job runs, `:backfill` is true, so the entire set is scanned. When delta jobs run, `:backfill` is false, and `:post_ids` is set.

## Why manual hinting is necessary

The badge grant query runs your badge query in a “subquery”. Often, the PostgreSQL optimizer struggles when scanning the full set when the clause is on the main query. While it may handle trivial queries well, it can falter with more complex aggregates.

To avoid potential issues, this constraint was added, allowing you to apply filters in the most appropriate location.

## Need help?

If you’re having trouble writing a badge query, post a question in #Support - describe what you’re trying to achieve and include your work in progress. The community will try to help.

> ℹ Badge triggers can be complex. Often, “daily” updates suffice, and you can skip the more intricate aspects of triggered queries.

> Last edited by @hugh 2024-07-16T03:15:08Z
> 
> > **Check document**
> >
> > Perform check on document:

---

_[View the full topic](https://meta.discourse.org/t/creating-triggered-custom-badge-queries/19336)._