What is SQL Badge Query for invited >25 People?

support
1

Hi, this is follow up my previous question from Copy-Paste Locked Badge Query (SQL) For More Than Champion or Invitation - #5 by iidbae

So, I want to give a badge to a member who has successful invited 25 people.
Just like the existing badge, the Campaigner (3 people) or Champion (5 people).
The reward is only a badge without raising the Trust Level.

The concept is similar with the SQL Badge Query in “A badge for 10 accepted answers” Discourse Solved (Accepted answer plugin) Only need changes the number.

I have see this "Promoter" Badge broken? and this https://github.com/discourse/discourse/blob/master/lib/badge_queries.rb , but still afraid and confuse to found the correct sql.
Maybe someone can help me.
Thank you very much.

2

The specific query for each invitation badge is generated with two variables:

  • How many people do they have to invite?
  • What TL do those people have to reach?

The answers for the existing badges are:

Badge People TL
Promoter 1 0
Campaigner 3 1
Champion 5 2

So for your new badge, take the SQL in the invite_badge method at https://github.com/discourse/discourse/blob/43f63c435d0098ffb0bc66c5544e026099818310/lib/badge_queries.rb#L136 and replace #{count.to_i} with the number of people and #{trust_level.to_i} with the TL.

4 Likes
3

Hi Andrew, thank you for your simple and easy explanation.
I am really appreciate that.

I just want to confirm if the below query is right:

      SELECT u.id user_id, current_timestamp granted_at
      FROM users u
      WHERE u.id IN (
        SELECT invited_by_id
        FROM invites i
        JOIN users u2 ON u2.id = i.user_id
        WHERE i.deleted_at IS NULL AND u2.active AND u2.trust_level >= 2 AND u2.silenced_till IS NULL
        GROUP BY invited_by_id
        HAVING COUNT(*) >= 25
      ) AND u.active AND u.silenced_till IS NULL AND u.id > 0 AND
        (:backfill OR u.id IN (:user_ids) )

So the query will automatically give a badge to a member who has successful invite 25 people when the TL is 2. Am I right?

Thank you very much. :pray:

4

Yes.

   

2 Likes