# Encourage 'Notify Users' flag PMs

**URL:** https://meta.discourse.org/t/encourage-notify-users-flag-pms/281549
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [October 31, 2020, 12:39pm UTC](https://meta.discourse.org/t/encourage-notify-users-flag-pms/281549 "2020-10-31T12:39:46Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![manuel](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/manuel/32/468169_2.png) [@manuel](https://meta.discourse.org/u/manuel)
#### Post date: [October 31, 2020, 12:39pm UTC](https://meta.discourse.org/t/encourage-notify-users-flag-pms/281549/1 "2020-10-31T12:39:46Z")

</div>

We want to encourage users to write more private messages about contentious or personal feedback on posts (and do it in a civil and friendly way..), rather than posting it public and possibly steering the conversation off-topic. Here’s the badge:

 ![helpful-badge](https://global.discourse-cdn.com/meta/original/3X/d/2/d2e064cd38478f2ff96a4b964f12274e333a35a5.png)

The query:

```plaintext
SELECT p.user_id, current_timestamp AS granted_at
FROM posts AS p
JOIN topics t on t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND t.title LIKE 'Your post in "%"'
AND p.post_number = 1
AND p.like_count >= 1
AND (:backfill OR p.user_id IN (:user_ids))
GROUP BY p.user_id
HAVING count(*) >=1

```

I didn’t find a specific trigger for flag messages, so the query uses the default title “Your post in …”. I’d say the badge is easy to game or cheat in several ways, but it would still achieve it’s goal just by giving more visiblity to this feature and communicating that this is regarded positive action for the community.

---

<div class="post-metadata">

### Author: ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [October 8, 2023, 7:12pm UTC](https://meta.discourse.org/t/encourage-notify-users-flag-pms/281549/2 "2023-10-08T19:12:43Z")

</div>

> [@manuel](#):
>
> I didn’t find a specific trigger for flag messages

Could this one be based on the `post_action` for ‘Notify User’?

Something like:

```sql
SELECT pa.user_id, current_timestamp AS granted_at
FROM post_actions pa
  JOIN posts p ON p.id = pa.related_post_id
WHERE pa.post_action_type_id = 6
  AND p.like_count >= 1
  AND (:backfill OR p.user_id IN (:user_ids))
GROUP BY pa.user_id
HAVING COUNT(*) >= 1

```

* * *

~~Actually, I think that links in the post that the flag was based on, not the PM. It may need a small tweak.~~

A quick change to the join to make `p.id = pa.related_post_id` does the job, I think. 👍

I think the ‘HAVING` is only needed for if you want to grant a Silver and Gold version of more than just one.