What cool badge queries have you come up with?

badge

(Kane York) #64

Change the beginning to one of the following:

SELECT user_id, current_timestamp granted_at
SELECT user_id, post.created_at granted_at
SELECT user_id, post.updated_at granted_at

Remember that bronze badges not “granted_at” the last ~48 hours will not give a notification.


(Divided By Zer0) #65

I had an issue with setting that to work on user edit or creation so I had to modify it a bit. If someone wants to do the same, you need to use the following code:

SELECT cf.user_id user_id, cf.updated_at granted_at
FROM user_custom_fields cf
WHERE cf.name = 'user_field_2'
AND (
  cf.user_id IN (:user_ids)
  OR :backfill
)
AND length(cf.value) > 1

(Divided By Zer0) #66

Is there a way to modify the above code so that the badge links to the post which granted the badge, similar to how “First Share” works?

Also, if I set that badge to be allowed to be given more than once, will the above code do it for multiple posts in the same thread? If not, how could I do that?

Thanks in advance :smile:


(PJH) #67

There is no post associated with filling out the custom fields, unless I’m missing something from your query.

But in general, to associate posts you need to tick the Query targets posts checkbox and include a posts.id (or post_id if it’s a foreign key) aliased to post_id and if you use the When a user edits or creates a post trigger you’ll need to include :backfill and :post_ids in the query (see this post futher up for an example)


(Divided By Zer0) #68

Gah, you’re right, sorry I referred the wrong code.

I got the following

SELECT
DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 110 AND
  (:backfill OR p.id IN (:post_ids) )

which I took from another tip in the thread. This automatically grants a user who posts in a specific post a badge. Would ticking “Query Target posts” link that to the badge?


(Kane York) #69

Yes, that query should be marked as targets posts, show post on the public badges page, and trigger on post creation.


(Divided By Zer0) #70

Thanks. Can you tell if the same query also grant the badge multiple times if the appropriate checkbox is ticket?


(PJH) #71

Yes, if that box is checked then multiple badges would be awarded.

May I ask why you have the DISTINCT ON (p.user_id) clause in there? If you’re after multiple badges that runs the risk of some of them not being assigned.


(Divided By Zer0) #72

I merely copied it from a post in here for assigning badges for on posting on a topic. It was this part which made me wonder if it can assign multiple badges. Do you think I can safely remove it?

EDIT: Just removed it and it seems to work fine. Thanks


(PJH) #73

Yes. It’s the difference between (for example):

[pjh@sofa ~]$ psql -d discourse -c "SELECT
> DISTINCT ON (p.user_id)
> p.user_id, p.id post_id, p.created_at granted_at
> FROM badge_posts p
> WHERE p.topic_id = 1000
> LIMIT 20"
 user_id | post_id |         granted_at         
---------+---------+----------------------------
      -1 |   34753 | 2014-07-19 17:21:48.438813
       4 |   52265 | 2014-08-08 01:16:23.26294
       6 |  186028 | 2015-01-02 18:05:22.61456
      15 |   63069 | 2014-08-21 20:21:46.150202
      16 |   31387 | 2014-07-16 12:51:37.874941
      17 |   80603 | 2014-09-13 00:25:50.532543
      18 |  120362 | 2014-10-15 13:47:01.920886
      20 |   12967 | 2014-06-19 15:49:18.492014
      28 |   13208 | 2014-06-19 20:36:59.371834
      30 |   35624 | 2014-07-21 17:28:57.3986
      33 |   89920 | 2014-09-21 14:20:44.817275
      43 |   13300 | 2014-06-19 21:18:48.065129
      50 |  143837 | 2014-11-06 05:17:54.776199
      60 |  154883 | 2014-11-18 21:49:56.792488
      69 |  428292 | 2015-05-22 17:21:00.939645
      86 |   14841 | 2014-06-20 22:46:50.493649
     110 |  120917 | 2014-10-15 19:32:08.440928
     123 |   51260 | 2014-08-07 03:31:05.654638
     183 |  160245 | 2014-11-25 21:12:52.132026
     261 |  187712 | 2015-01-05 21:24:05.746039
(20 rows)
[pjh@sofa ~]$ psql -d discourse -c "SELECT
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 1000
LIMIT 20"
 user_id | post_id |         granted_at         
---------+---------+----------------------------
     589 |  195565 | 2015-01-13 16:11:05.705177
     294 |  430986 | 2015-05-25 17:01:07.148368
     606 |  430988 | 2015-05-25 17:01:13.176588
     606 |  430990 | 2015-05-25 17:01:40.033308
     762 |   37144 | 2014-07-22 19:04:02.591978
     579 |  430599 | 2015-05-25 14:33:06.928209
     294 |  430989 | 2015-05-25 17:01:32.901402
     922 |  430109 | 2015-05-25 00:28:35.562221
     922 |  430027 | 2015-05-24 21:57:25.817651
     606 |  430111 | 2015-05-25 00:29:11.922553
     606 |  430026 | 2015-05-24 21:56:50.236247
     579 |  430025 | 2015-05-24 21:56:46.181157
     922 |  418379 | 2015-05-14 07:41:03.747217
     123 |  209061 | 2015-01-26 03:49:34.27203
     294 |  430991 | 2015-05-25 17:02:00.277703
     762 |  125242 | 2014-10-19 16:24:21.902349
     606 |  145216 | 2014-11-07 14:42:28.916019
     922 |  431105 | 2015-05-25 20:30:49.410084
     294 |  431106 | 2015-05-25 20:31:18.747891
     294 |  431103 | 2015-05-25 20:30:45.923232
(20 rows)

[pjh@sofa ~]$ 

Note the repetition of user 922 in the second query - that’s what’s required to give more than one badge.

The first appears to pick (semi) random post numbers to apply badges to


(Alessio Fattorini) #74

How can I create badges that count totally posts of someone?
And a badge for days attendance (not consecutive)
Any idea?


(PJH) #75

Including

  • PM’s,
  • flags
  • deleted/hidden posts
  • categories that require logging in to view?

For someone who’s made 1,000 or more posts:

SELECT user_id, current_timestamp granted_at 
FROM posts  
GROUP BY user_id 
HAVING count(*) >= 1000

Triggered daily, don’t target posts.

To exclude those things listed in the bullet points, change from posts to from badge_posts

For visiting 365 days:

SELECT user_id, current_timestamp granted_at 
FROM user_visits  
GROUP BY user_id 
HAVING count(*) >= 365

Again, triggered daily, don’t target posts.


(Alessio Fattorini) #76

Why are you using pow? Is it faster than say just 20000 posts?
Can I do the same thing for whole topics?


(PJH) #77

No. It’s to make them self documenting, since those particular badges on our instance are based on 3^3, 3^4, 3^5, 3^6 etc, (i.e. because 9, 27, 81, 243 and higher are less readable when glancing at the query.)

If you’re basing yours on powers of 10, then simply use 10000, 20000, 30000 or whatever.

Yes, but

  1. Do you mean badges for one single topic?
  • All topics within a single category?

If the first, then that’s easy, the topic_id is in post_timings:

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
WHERE :backfill
   AND topic_id=<TOPICNum>
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10)  /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC

But you’d need a separate set of badges for each topic you want to count posts in.

If it’s the second - all topics within a category then you’re going to need to join against the topics table to get the category_id you’re looking for:

SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at
FROM post_timings pt
JOIN topics t on t.id=pt.topic_id
WHERE :backfill
   AND  t.category_id=<CATEGORYNum>
GROUP BY pt.user_id
HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10)  /* Change the 9 and 10 (x and x+1) for the different badges */
ORDER BY c DESC

Ditto for the above though - you’d need a separate set of badges for each category you want badges for (unless you do AND t.category_id in (<CATNO1>, <CATNO2>, <CATNO3>))


Note, however, that these queries are expensive in the time they take to execute. Run them at most once a day.


(Alessio Fattorini) #78

Amazing! Thanks very much!


(Alessio Fattorini) #79

Sorry, I mean check if someone has read a specific topic (like the welcoming topic). just the first post or whole discussion


(PJH) #80

[quote=“alefattorini, post:79, topic:18978”]
Sorry, I mean check if someone has read a specific topic (like the welcoming topic). just the first post [/quote]

We’ve already sorta covered that - start reading near this post:


(Alessio Fattorini) #81

Cool man, really! Now I have all what I need


(Felix Freiberger) #82

As already mentioned here:

Grant a badge to everyone having posted at least <POST_COUNT> posts in the category <CATEGORY_NAME> that have received at least <LIKE_COUNT> likes by users in the group <TEAM_NAME>. Similar to the bug reporter badge here, but can require more than one like.

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 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 '<TEAM_NAME>' ) 
            )
    ) >= <LIKE_COUNT>
    AND p.post_number = 1
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST_COUNT>

(Felix Freiberger) #83

This badge detects whether a user has enabled mailing list mode:

SELECT u.id user_id, current_timestamp granted_at
FROM users u
WHERE mailing_list_mode = true
AND (:backfill OR u.id IN (:user_ids))

It should trigger when a user is created or edited.