Die Standard-Badge-Abfragen

JammyDodger · 27. August 2023 um 09:09

Dies ist eine Referenzanleitung für die SQL-Abfragen der Standard-Badges und deren Trigger-Informationen (sofern verfügbar).

Kern-Badges

Jahrestag

(Dieser enthält einige zusätzliche Backend-Magie zur Auswahl der Daten, aber ich nehme ihn trotzdem auf)

 start_date = start_date.iso8601(6)
    end_date = end_date.iso8601(6)

      SELECT u.id
        FROM users AS u
        JOIN posts AS p ON p.user_id = u.id
        JOIN topics AS t ON p.topic_id = t.id
       WHERE u.id > 0
         AND u.active
         AND NOT u.staged
         AND (u.silenced_till IS NULL OR u.silenced_till < '#{start_date}')
         AND (u.suspended_till IS NULL OR u.suspended_till < '#{start_date}')
         AND u.created_at <= '#{start_date}'
         AND NOT p.hidden
         AND p.deleted_at IS NULL
         AND p.created_at BETWEEN '#{start_date}' AND '#{end_date}'
         AND t.visible
         AND t.archetype <> 'private_message'
         AND t.deleted_at IS NULL
         AND NOT EXISTS (SELECT 1 FROM user_badges AS ub WHERE ub.user_id = u.id AND ub.badge_id = #{Badge::Anniversary} AND ub.granted_at BETWEEN '#{start_date}' AND '#{end_date}')
         AND NOT EXISTS (SELECT 1 FROM anonymous_users AS au WHERE au.user_id = u.id)
       GROUP BY u.id
      HAVING COUNT(p.id) > 0


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Geschätzt (Anzahl der Likes auf mehreren Beiträgen)

Die Geschätzt, Respektiert und Bewundert Badges folgen demselben Muster, jedoch mit unterschiedlichen Werten für p.like_count und HAVING COUNT(*).

SELECT p.user_id, CURRENT_TIMESTAMP AS granted_at
      FROM posts AS p
      WHERE p.like_count >= #{like_count}
        AND (:backfill OR p.user_id IN (:user_ids))
      GROUP BY p.user_id
      HAVING COUNT(*) > #{post_count}


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Autobiograf

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
          (:backfill OR u.id IN (:user_ids) )


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer bearbeitet oder erstellt wird

Basic (Vertrauensstufen)

Die Basic, Member, Regular, Leader Badges folgen alle demselben Muster, jedoch mit einem anderen trust_level-Wert.

SELECT u.id user_id, current_timestamp granted_at FROM users u
      WHERE trust_level >= #{level.to_i} AND (
        :backfill OR u.id IN (:user_ids)
      )


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer die Vertrauensstufe ändert

Zertifiziert und Lizenziert

Diese haben keine SQL-Abfrage. Sie sind Teil des Discourse Narrative Bot und werden programmgesteuert vergeben, wenn ein Benutzer die interaktiven Tutorials (Discobot) abgeschlossen hat.

Editor

 SELECT p.user_id, min(p.id) post_id, min(p.created_at) granted_at
    FROM badge_posts p
    WHERE p.self_edits > 0 AND
        (:backfill OR p.id IN (:post_ids) )
    GROUP BY p.user_id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer einen Beitrag bearbeitet oder erstellt

Enthusiast

Die Enthusiast, Aficionado und Devotee Badges folgen demselben Muster, jedoch mit einem anderen HAVING COUNT(*)-Schwellenwert.

WITH consecutive_visits AS (
        SELECT user_id
             , visited_at
             , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s
          FROM user_visits
      ), visits AS (
        SELECT user_id
             , MIN(visited_at) "start"
             , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank"
          FROM consecutive_visits
      GROUP BY user_id, s
        HAVING COUNT(*) >= #{days}
      )
      SELECT user_id
           , "start" + interval '#{days} days' "granted_at"
        FROM visits
       WHERE "rank" = 1


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Erstes Emoji

Es gibt keine SQL-Abfrage – das Badge wird in dem Moment vergeben, in dem ein qualifizierender Beitrag über CookedPostProcessor#grant_badges verarbeitet wird.

Kriterium: Der zubereitete Beitrag muss mindestens ein img.emoji-Element enthalten, das sich nicht innerhalb eines aside.quote-Blocks befindet. Mit anderen Worten: Emojis, die direkt in den Beitragstext eingegeben werden, zählen; Emojis, die nur innerhalb eines zitierten Abschnitts erscheinen, zählen nicht.


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Beitrag zubereiten (CookedPostProcessor)

Erste Flagge

SELECT pa1.user_id, pa1.created_at granted_at, pa1.post_id
    FROM (
      SELECT pa.user_id, MIN(pa.id) id
      FROM post_actions pa
      JOIN badge_posts p on p.id = pa.post_id
      WHERE post_action_type_id IN (
        SELECT f.id
        FROM flags f
        WHERE name != 'like'
        AND score_type IS FALSE
        AND require_message IS FALSE
      )
      AND (:backfill OR pa.post_id IN (:post_ids))
      GROUP BY pa.user_id
    ) x
    JOIN post_actions pa1 on pa1.id = x.id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer auf einen Beitrag reagiert

Erstes Like

SELECT pa1.user_id, pa1.created_at granted_at, pa1.post_id
    FROM (
      SELECT pa.user_id, MIN(pa.id) id
      FROM post_actions pa
      JOIN badge_posts p on p.id = pa.post_id
      WHERE post_action_type_id = 2 AND
        (:backfill OR pa.post_id IN (:post_ids) )
      GROUP BY pa.user_id
    ) x
    JOIN post_actions pa1 on pa1.id = x.id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer auf einen Beitrag reagiert

Erster Link

SELECT l.user_id, l.post_id, l.created_at granted_at
    FROM
    (
      SELECT MIN(l1.id) id
      FROM topic_links l1
      JOIN badge_posts p1 ON p1.id = l1.post_id
      JOIN badge_posts p2 ON p2.id = l1.link_post_id
      WHERE NOT reflection AND p1.topic_id <> p2.topic_id AND not quote AND
        (:backfill OR ( p1.id in (:post_ids) ))
      GROUP BY l1.user_id
    ) ids
    JOIN topic_links l ON l.id = ids.id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer einen Beitrag bearbeitet oder erstellt

Erste Erwähnung

SELECT acting_user_id AS user_id, MIN(target_post_id) AS post_id, MIN(p.created_at) AS granted_at
    FROM user_actions
    JOIN posts p ON p.id = target_post_id
    JOIN topics t ON t.id = topic_id
    JOIN categories c on c.id = category_id
    WHERE action_type = 7
      AND NOT read_restricted
      AND p.deleted_at IS  NULL
      AND t.deleted_at IS  NULL
      AND t.visible
      AND t.archetype <> 'private_message'
      AND (:backfill OR p.id IN (:post_ids))
    GROUP BY acting_user_id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer einen Beitrag bearbeitet oder erstellt

Erste Onebox

Es gibt keine SQL-Abfrage. Wird während der Beitragzubereitung über CookedPostProcessor#grant_badges vergeben.

Kriterium: Der zubereitete Beitrag muss mindestens eine Onebox erzeugt haben – eine reiche Linkvorschau, die generiert wird, wenn eine nackte URL in einer eigenen Zeile auf eine einbettbare Ressource verweist.


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Beitrag zubereiten (`CookedPostProcessor`)

Erstes Zitat

SELECT ids.user_id, q.post_id, p3.created_at granted_at
    FROM
    (
      SELECT p1.user_id, MIN(q1.id) id
      FROM quoted_posts q1
      JOIN badge_posts p1 ON p1.id = q1.post_id
      JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
      WHERE (:backfill OR ( p1.id IN (:post_ids) ))
      GROUP BY p1.user_id
    ) ids
    JOIN quoted_posts q ON q.id = ids.id
    JOIN badge_posts p3 ON q.post_id = p3.id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer einen Beitrag bearbeitet oder erstellt

Erste Antwort per E-Mail

Es gibt keine SQL-Abfrage. Wird während der Beitragzubereitung über CookedPostProcessor#grant_badges vergeben.

Kriterium: post.is_reply_by_email? muss true sein – das heißt, der Beitrag wurde durch Beantwortung einer Discourse-Benachrichtigungs-E-Mail eingereicht und nicht über die Weboberfläche. Die Funktion für eingehende E-Mail-Antworten muss auf der Seite aktiviert sein.


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Beitrag zubereiten (`CookedPostProcessor`)

Ursprünglicher Community-Vorschlag 'Reply by email' badge - #3 by lrossouw

Erster Share

SELECT views.user_id, i2.post_id, i2.created_at granted_at
    FROM
    (
      SELECT i.user_id, MIN(i.id) i_id
      FROM incoming_links i
      JOIN badge_posts p on p.id = i.post_id
      JOIN users u on u.id = i.user_id
      GROUP BY i.user_id
    ) as views
    JOIN incoming_links i2 ON i2.id = views.i_id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Neuer Benutzer des Monats

Es gibt keine SQL-Abfrage, die dem Badge zugeordnet ist. Wird durch einen täglich geplanten Job (Jobs::GrantNewUserOfTheMonthBadges) vergeben. Der Job läuft jeden Tag, vergibt aber nur für den vorherigen Kalendermonat und nur einmal pro Monat.

Eignung: Der Kandidat muss sein Konto während des vorherigen Kalendermonats erstellt haben, aktiv und nicht gestaffelt sein, kein Administrator oder Moderator sein und nicht gesperrt sein. Er muss zudem in mindestens 2 verschiedenen Themen mit insgesamt mindestens 2 Beiträgen gepostet und mindestens 2 Likes erhalten haben.

Bewertung: Kandidaten werden nach einem gewichteten Like-Score eingestuft. Jedes erhaltene Like wird mit dem Vertrauensniveau der Person gewichtet, die es gegeben hat:

Liker	Gewicht
Administrator oder Moderator	3.0
Vertrauensstufe 4	2.0
Vertrauensstufe 3	1.5
Vertrauensstufe 2	1.0
Vertrauensstufe 1	0.25
Vertrauensstufe 0	0.1

Der endgültige Score ist SUM(gewichtete Likes) / (5 + Anzahl der Beiträge) – die Division durch die Anzahl der Beiträge dämpft den Vorteil von sehr aktiven Poster. Bis zu 2 Benutzer können den Badge pro Monat erhalten. Jeder Gewinner erhält zudem eine Systemnachricht.


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich geplanter Job (vergabe für vorherigen Kalendermonat)

Netter Beitrag (Likes auf einem Beitrag)

Die Netter Beitrag, Guter Beitrag und Großer Beitrag Badges folgen alle demselben Muster, jedoch mit einem anderen Schwellenwert für p.like_count.

SELECT p.user_id, p.id post_id, CURRENT_TIMESTAMP granted_at
      FROM badge_posts p
      WHERE p.post_number > 1 AND p.like_count >= #{count.to_i} AND
        (:backfill OR p.id IN (:post_ids) )


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer auf einen Beitrag reagiert

Netter Share (Link-Sharing)

Die Netter Share, Guter Share und Großer Share Badges folgen alle demselben Muster, jedoch mit einem anderen HAVING COUNT(*)-Schwellenwert.

SELECT views.user_id, i2.post_id, CURRENT_TIMESTAMP granted_at
      FROM
      (
        SELECT i.user_id, MIN(i.id) i_id
        FROM incoming_links i
        JOIN badge_posts p on p.id = i.post_id
        JOIN users u on u.id = i.user_id
        GROUP BY i.user_id,i.post_id
        HAVING COUNT(DISTINCT(i.ip_address, i.current_user_id)) >= #{count}
      ) as views
      JOIN incoming_links i2 ON i2.id = views.i_id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Nettes Thema (Likes auf einem Thema)

Die Nettes Thema, Gutes Thema und Großes Thema Badges folgen alle demselben Muster, jedoch mit einem anderen Schwellenwert für p.like_count.

SELECT p.user_id, p.id post_id, CURRENT_TIMESTAMP granted_at
      FROM badge_posts p
      WHERE p.post_number = 1 AND p.like_count >= #{count.to_i} AND
        (:backfill OR p.id IN (:post_ids) )


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer auf einen Beitrag reagiert

Aus Liebe (Max Likes an einem Tag)

Die Aus Liebe, Höhere Liebe und Verrückt nach Liebe Badges folgen alle demselben Muster, jedoch mit einem anderen Wert für den HAVING COUNT(*)-Schwellenwert.

SELECT gdl.user_id, CURRENT_TIMESTAMP AS granted_at
      FROM given_daily_likes AS gdl
      WHERE gdl.limit_reached
        AND (:backfill OR gdl.user_id IN (:user_ids))
      GROUP BY gdl.user_id
      HAVING COUNT(*) >= #{count}


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Beliebter Link (Link-Klicks)

Die Beliebter Link, Heißer Link und Berühmter Link folgen alle demselben Muster, jedoch mit einem anderen tl.clicks-Schwellenwert.

SELECT tl.user_id, post_id, CURRENT_TIMESTAMP granted_at
        FROM topic_links tl
        JOIN badge_posts p ON p.id = post_id
       WHERE NOT tl.internal
         AND tl.clicks >= #{count}
      GROUP BY tl.user_id, tl.post_id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Promoter (Einladungen)

Die Promoter, Campaigner und Champion Badges folgen alle demselben Muster, jedoch mit einem anderen Vertrauensstufenwert, der für die Eingeladenen erforderlich ist, und einem anderen HAVING COUNT(*)-Schwellenwert.

SELECT u.id user_id, CURRENT_TIMESTAMP granted_at
      FROM users u
      WHERE u.id IN (
        SELECT invited_by_id
        FROM invites i
        JOIN invited_users iu ON iu.invite_id = i.id
        JOIN users u2 ON u2.id = iu.user_id
        WHERE i.deleted_at IS NULL
        AND i.invited_by_id <> u2.id
        AND u2.active
        AND u2.trust_level >= #{trust_level.to_i}
        AND u2.silenced_till IS NULL
        GROUP BY invited_by_id
        HAVING COUNT(*) >= #{count.to_i}
      ) AND u.active AND u.silenced_till IS NULL AND u.id > 0 AND
      (:backfill OR u.id IN (:user_ids) )


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Richtlinien lesen

 SELECT user_id, read_faq granted_at
    FROM user_stats
    WHERE read_faq IS NOT NULL AND (user_id IN (:user_ids) OR :backfill)


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer bearbeitet oder erstellt wird

Leser

SELECT id user_id, CURRENT_TIMESTAMP granted_at
    FROM users
    WHERE id IN
    (
      SELECT pt.user_id
      FROM post_timings pt
      JOIN badge_posts b ON b.post_number = pt.post_number AND
                            b.topic_id = pt.topic_id
      JOIN topics t ON t.id = pt.topic_id
      LEFT JOIN user_badges ub ON ub.badge_id = 17 AND ub.user_id = pt.user_id
      WHERE ub.id IS NULL AND t.posts_count > 100
      GROUP BY pt.user_id, pt.topic_id, t.posts_count
      HAVING COUNT(*) >= t.posts_count


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger

Danke (Likes gegeben + Likes erhalten)

Die Danke, Gibt zurück und Empathisch Badges folgen alle demselben Muster, jedoch mit unterschiedlichen Werten für us.likes_given und HAVING COUNT(*).

SELECT us.user_id, CURRENT_TIMESTAMP granted_at
      FROM user_stats AS us
      INNER JOIN posts AS p ON p.user_id = us.user_id
      WHERE p.like_count > 0
        AND us.likes_given >= #{likes_given}
        AND (:backfill OR us.user_id IN (:user_ids))
      GROUP BY us.user_id, us.likes_given
      HAVING COUNT(*) > #{likes_received}


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Täglich aktualisieren

Willkommen

SELECT p.user_id, MIN(post_id) post_id, MIN(pa.created_at) granted_at
    FROM post_actions pa
    JOIN badge_posts p on p.id = pa.post_id
    WHERE post_action_type_id = 2 AND
        (:backfill OR pa.post_id IN (:post_ids) )
    GROUP BY p.user_id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer auf einen Beitrag reagiert

Wiki-Editor

SELECT pr2.user_id, pr2.post_id, pr2.created_at granted_at
    FROM
    (
      SELECT MIN(pr.id) id
      FROM post_revisions pr
      JOIN badge_posts p on p.id = pr.post_id
      WHERE p.wiki
          AND NOT pr.hidden
          AND (:backfill OR p.id IN (:post_ids))
      GROUP BY pr.user_id
    ) as X
    JOIN post_revisions pr2 ON pr2.id = X.id


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer einen Beitrag bearbeitet oder erstellt

Discourse Reactions

Erste Reaktion

SELECT user_id, created_at AS granted_at, post_id
  FROM (
           SELECT ru.post_id, ru.user_id, ru.created_at,
                  ROW_NUMBER() OVER (PARTITION BY ru.user_id ORDER BY ru.created_at) AS row_number
           FROM discourse_reactions_reaction_users ru
                JOIN badge_posts p ON ru.post_id = p.id
           WHERE :backfill
              OR ru.post_id IN (:post_ids)
       ) x
  WHERE row_number = 1


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer einen Beitrag bearbeitet oder erstellt

Discourse Solved

Gelöst!

 SELECT post_id, user_id, created_at AS granted_at
  FROM (
           SELECT p.id AS post_id, p.user_id, dsst.created_at,
              ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY dsst.created_at) AS row_number
           FROM discourse_solved_solved_topics dsst
              JOIN badge_posts p ON dsst.answer_post_id = p.id
              JOIN topics t ON p.topic_id = t.id
           WHERE p.user_id <> t.user_id -- Themen ignorieren, die vom OP gelöst wurden
              AND (:backfill OR p.id IN (:post_ids))
       ) x
  WHERE row_number = 1


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer einen Beitrag bearbeitet oder erstellt

Beratungslehrer

Die Badges für Beratungslehrer, Alleswisser und Lösungs-Institution folgen alle demselben Muster, jedoch mit einem anderen HAVING COUNT (*) >=-Schwellenwert:

SELECT p.user_id, MAX(pcf.created_at) AS granted_at
   FROM post_custom_fields pcf
        JOIN badge_posts p ON pcf.post_id = p.id
        JOIN topics t ON p.topic_id = t.id
   WHERE pcf.name = 'is_accepted_answer'
     AND p.user_id <> t.user_id -- Themen ignorieren, die vom OP gelöst wurden
     AND (:backfill OR p.id IN (:post_ids))
   GROUP BY p.user_id
   HAVING COUNT(*) >= #{min_count}


Sperrabfrage täglich ausführen
Abfrage zielt auf Beiträge ab
Trigger	Wenn ein Benutzer einen Beitrag bearbeitet oder erstellt

Discourse Github

Diese kann ich nicht lesen.

github.com/discourse/discourse-github