That’s exactly what you would do
I have been reading through and been looking for two in particular
- One to check if you have posted a topic in a particular category - like introductions
- One to check if you have filled in particular user_fields such as location, about me and added profile picture etc.
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
I made a script to create sets of badges like this:
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
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
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.
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
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. . .
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
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.
How do you get a badge granted when a user do the konami code ?
Anyone done a reply by email badge?
I’m not too sure, but it shouldn’t be too hard considering the
Post table has
boolean. Find Posts where
true and get the
user_id of that post!
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
That is a great idea for a badge, we should add it for our next release (adding it to the list)
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
Slightly off-topic, but are there any updates in reply-by-email being automatically setup for hosted sites?
Still on our roadmap, we are working on it, should be done in a 4-12 weeks
Is it possible to create a badge for the user posting specific text within their post?