# Badge for posts with Likes from a specific group

**URL:** <https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728>\
**Category:** Data & reporting\
**Tags:** sql-triggered-badge\
**Created:** [August 14, 2015, 8:44pm UTC](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728 "2015-08-14T20:44:45Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![Lilly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/lilly/32/575047_2.png) [@Lilly](https://meta.discourse.org/u/Lilly)\
**Post date:** [August 25, 2023, 12:10am UTC](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728/4 "2023-08-25T00:10:56Z")

</div>

Hi @Firepup650🙂 maybe try this one. it worked on my instance.

```plaintext
<CATEGORY NAME> = Case sensitive category name (not slug)
<GROUP> = Group Name (ie: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = minimum # of likes you want to set
<POST COUNT THRESHOLD> = minimum # of posts

```

```sql
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 t.category_id = (
        SELECT id FROM categories WHERE name ilike '<CATEGORY NAME>'
    ) AND (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<GROUP NAME>' ) 
            )
    ) >= <MINIMUM LIKE COUNT>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>

```

for multiple categories you can do this:

```plaintext
<CATEGORY NAMES> = Case sensitive category names
<GROUP> = Group Name (ie: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = minimum # of likes you want to set
<POST COUNT THRESHOLD> = minimum # of posts

```

```sql
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 t.category_id IN (
        SELECT id FROM categories WHERE name ILIKE ANY (ARRAY['<CATEGORY NAME 1>', '<CATEGORY NAME 2>', '<CATEGORY NAME 3>'])
    ) AND (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GROUP>' ) 
            )
    ) >= <MINIMUM LIKE COUNT>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>

```

---

_[View the full topic](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728)._