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

data & reporting
1

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.
2

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.

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

3 Likes
3

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 :slight_smile: and should be returning my account name

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

4

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
5

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

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):

  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. . .

4 Likes
6

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

7

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

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