# Badges for 'post in specific category' and 'filled in bio fields'

**URL:** <https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732>\
**Category:** Data & reporting\
**Tags:** sql-triggered-badge\
**Created:** [2016年三月2日 14:37 UTC](https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732 "2016-03-02T14:37:07Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![mikechristopher](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mikechristopher/32/89135_2.png) [@mikechristopher](https://meta.discourse.org/u/mikechristopher)\
**Post date:** [2016年三月2日 14:37 UTC](https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732/1 "2016-03-02T14:37:07Z")

</div>

Hi Guys,  
I have been reading through and been looking for two in particular

1. One to check if you have posted a topic in a particular category - like introductions
2. One to check if you have filled in particular user\_fields such as location, about me and added profile picture etc.

---

<div class="post-metadata">

**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\
**Post date:** [2016年三月2日 16:32 UTC](https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732/2 "2016-03-02T16:32:52Z")

</div>

> [@mikechristopher](#):
>
> One to check if you have posted a topic in a particular category - like introductions

This matches if a post with a particular tag has been posted in a particular category. You can remove the `AND tcf.name ...` line 3 from the bottom if you don’t care about tags.

```plaintext
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
JOIN topic_custom_fields tcf on t.id = tcf.topic_id
WHERE category_id = ANY (
  SELECT id FROM categories WHERE name ilike 'CATEGORY-NAME'
) AND p.post_number = 1
AND tcf.name LIKE 'tags' and tcf.value LIKE 'TAG-NAME'
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id

```

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

---

<div class="post-metadata">

**Author:** ![mikechristopher](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mikechristopher/32/89135_2.png) [@mikechristopher](https://meta.discourse.org/u/mikechristopher)\
**Post date:** [2016年三月8日 12:43 UTC](https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732/3 "2016-03-08T12:43:09Z")

</div>

> [@mikechristopher](#):
>
> One to check if you have filled in particular user\_fields such as location, about me and added profile picture etc.

I am sure I am going wrong here somewhere - This is what I am trying to use:

```
SELECT user, current_timestamp granted_at
FROM user_profiles
WHERE location IS NOT NULL

```

However is only returning

current\_user granted\_at  
discourse 2016-03-08 12:40:44.57532+00

Which is certainly not correct as discourse is not a member name 🙂 and should be returning my account name

EDIT: Found the issue I needed to use user\_id rather than user

---

<div class="post-metadata">

**Author:** ![mikechristopher](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mikechristopher/32/89135_2.png) [@mikechristopher](https://meta.discourse.org/u/mikechristopher)\
**Post date:** [2016年三月8日 12:57 UTC](https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732/4 "2016-03-08T12:57:38Z")

</div>

Need some feedback please - would it be better to run the following 3 queries seperately to create badges and then check those badges have been granted to grant a seperate badge again. Or could someone please assist me in merging these 3 queries into 1 in order to have it grant a badge if each part of it has been done.

Thanks,

```
SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts bp
INNER JOIN user_profiles up
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 5

SELECT user_id, current_timestamp granted_at
FROM user_profiles
WHERE location IS NOT NULL

SELECT user_id, current_timestamp granted_at
FROM user_profiles
WHERE bio_raw IS NOT NULL

```

---

<div class="post-metadata">

**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\
**Post date:** [2016年三月8日 20:57 UTC](https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732/5 "2016-03-08T20:57:16Z")

</div>

I can’t quite figure out the first query. Can you give it in English? Is that supposed to be “made 5 posts?”

The second two:

## Users with a BIO and LOCATION

```plaintext
SELECT user_id, current_timestamp granted_at
FROM user_profiles up
WHERE location IS NOT NULL AND
bio_raw IS NOT NULL
AND (:backfill OR up.user_id IN (:user_ids) )

```

This version, based on the Autobiographer badge with the location requirement added might be a bit better, though, as it checks that the bio is a certain lenght (remove the avatar requirement if you like):

```plaintext
  SELECT u.id user_id, current_timestamp granted_at
    FROM users u
    JOIN user_profiles up on u.id = up.user_id
    WHERE bio_raw IS NOT NULL AND LENGTH(TRIM(bio_raw)) > 10 AND
          uploaded_avatar_id IS NOT NULL AND
          up.location IS NOT NULL AND
          (:backfill OR u.id IN (:user_ids) )

```

But I think this was the easy part. . .

---

<div class="post-metadata">

**Author:** ![mikechristopher](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mikechristopher/32/89135_2.png) [@mikechristopher](https://meta.discourse.org/u/mikechristopher)\
**Post date:** [2016年三月8日 21:16 UTC](https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732/6 "2016-03-08T21:16:40Z")

</div>

Yeah the first one was to check that 5 posts have been made - someone posted it up earlier but was related to 1000 posts which I just modified. Thank you for providing the last one that’s exactly what I was after. It doesn’t include gravatar however does it so I can just add that in.

What does the :backfill do in the queries?

Appreciate your help @pfaffman

---

<div class="post-metadata">

**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\
**Post date:** [2016年三月8日 21:32 UTC](https://meta.discourse.org/t/badges-for-post-in-specific-category-and-filled-in-bio-fields/276732/7 "2016-03-08T21:32:02Z")

</div>

> [@mikechristopher](#):
>
> Yeah the first one was to check that 5 posts have been made - someone posted it up earlier but was related to 1000 posts which I just modified.

Got it. Yeah. I tried pasting those together and couldn’t quite get it.

> [@mikechristopher](#):
>
> What does the :backfill do in the queries?

I can’t explain in a cogent way. I think that you need it for any query that targets posts.