What cool badge queries have you come up with?

badge

(Kane York) #108

That’s exactly what you would do :slightly_smiling:


(Kane York) #109

Most commonly used words

by @meglio


(Anton) #110

#111

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.

Thanks,
Mike


(Jay Pfaffman) #112

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


(Jay Pfaffman) #113

I made a script to create sets of badges like this:


#114

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


#115

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

(Jay Pfaffman) #116

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


#117

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


(Jay Pfaffman) #118

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.


(Slevin Kelevra) #119

Hey !

How do you get a badge granted when a user do the konami code ?


(Louis Rossouw) #120

Anyone done a reply by email badge?


(Steven Slade) #121

I’m not too sure, but it shouldn’t be too hard considering the Post table has via_email boolean. Find Posts where via_email is true and get the user_id of that post!


(Louis Rossouw) #122

Here is a Reply by Email Badge:

select
    p.user_id
    ,min(p.created_at) granted_at
    ,min(p.id) post_id
from badge_posts p
where
    (:backfill or p.id IN (:post_ids) ) 
    and p.post_number >1
    and p.via_email=True
group by 
    p.user_id


(Sam Saffron) #123

That is a great idea for a badge, we should add it for our next release (adding it to the list)


(Louis Rossouw) #124

Could also do a topic created via email badge (but I haven’t activated that functionality).
Probably something like:

select
    p.user_id
    ,min(p.created_at) granted_at
    ,min(p.id) post_id
from badge_posts p
where
    (:backfill or p.id IN (:post_ids) ) 
    and p.post_number =1
    and p.via_email=True
group by 
    p.user_id

(Joshua Rosenfeld) #125

Slightly off-topic, but are there any updates in reply-by-email being automatically setup for hosted sites?


(Sam Saffron) #126

Still on our roadmap, we are working on it, should be done in a 4-12 weeks :slight_smile:


#127

Is it possible to create a badge for the user posting specific text within their post?