Les requêtes de badge par défaut

[JammyDodger]

Voici un guide de référence pour les requêtes SQL des badges par défaut et leurs informations de déclenchement (lorsqu’elles sont disponibles).

Badges principaux

Anniversaire

(Celui-ci comporte une logique backend supplémentaire pour choisir les dates, mais je l’inclus quand même)

 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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Apprécié (nombre de J’aime sur plusieurs publications)

Les badges Apprécié, Respecté et Admiré suivent le même modèle, mais avec des valeurs différentes pour p.like_count et 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}

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Autobiographe

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lors de la création ou de la modification d’un utilisateur

Basique (Niveaux de confiance)

Les badges Basique, Membre, Régulier et Leader suivent tous le même modèle, mais avec une valeur trust_level différente.

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur change de niveau de confiance

Certifié et Licencié

Ces badges n’ont aucune requête SQL. Ils font partie du Discourse Narrative Bot et sont attribués de manière programmatique lorsqu’un utilisateur termine les tutoriels interactifs (Discobot).

Éditeur

 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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur modifie ou crée une publication

Passionné

Les badges Passionné, Afficionado et Dévot suivent le même modèle, mais avec un seuil différent pour HAVING COUNT(*).

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Premier Emoji

Il n’y a pas de requête SQL — le badge est attribué dès qu’une publication éligible est traitée via CookedPostProcessor#grant_badges.

Critère : La publication cuisinée doit contenir au moins un élément img.emoji qui n’est pas à l’intérieur d’un bloc aside.quote. Autrement dit, les emojis tapés directement dans le corps de la publication comptent ; les emojis apparaissant uniquement dans un passage cité ne comptent pas.

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Cuisson de la publication (CookedPostProcessor)

Premier Drapeau

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur agit sur une publication

Premier J’aime

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur agit sur une publication

Premier Lien

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur modifie ou crée une publication

Première Mention

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur modifie ou crée une publication

Premier Onebox

Il n’y a pas de requête SQL. Attribué lors de la cuisson de la publication via CookedPostProcessor#grant_badges.

Critère : La publication cuisinée doit avoir généré au moins un onebox — un aperçu de lien enrichi créé lorsqu’une URL brute sur une ligne unique correspond à une ressource intégrable.

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Cuisson de la publication (CookedPostProcessor)

Premier Citation

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur modifie ou crée une publication

Première Réponse par E-mail

Il n’y a pas de requête SQL. Attribué lors de la cuisson de la publication via CookedPostProcessor#grant_badges.

Critère : post.is_reply_by_email? doit être true — c’est-à-dire que la publication a été soumise en répondant à un e-mail de notification Discourse plutôt que via l’interface web. La fonctionnalité de réponse par e-mail entrant doit être activée sur le site.

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Cuisson de la publication (CookedPostProcessor)

Suggestion originale de la communauté 'Reply by email' badge - #3 by lrossouw

Premier Partage

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Nouvel Utilisateur du Mois

Aucune requête SQL n’est enregistrée pour ce badge. Il est attribué par un tâche planifiée quotidienne (Jobs::GrantNewUserOfTheMonthBadges). La tâche s’exécute chaque jour, mais attribue le badge uniquement pour le mois civil précédent, et une seule fois par mois.

Éligibilité : Le candidat doit avoir créé son compte pendant le mois civil précédent, être actif et non mis en scène, ne pas être administrateur ou modérateur, et ne pas être suspendu. Il doit également avoir publié dans au moins 2 sujets distincts avec au moins 2 publications au total, et avoir reçu au moins 2 J’aime.

Score : Les candidats sont classés selon un score pondéré basé sur les J’aime. Chaque J’aime reçu est pondéré par le niveau de confiance de la personne qui l’a donné :

J’aimeur	Poids
Administrateur ou Modérateur	3.0
Niveau de confiance 4	2.0
Niveau de confiance 3	1.5
Niveau de confiance 2	1.0
Niveau de confiance 1	0.25
Niveau de confiance 0	0.1

Le score final est SUM(J'aimes pondérés) / (5 + nombre de publications) — la division par le nombre de publications atténue l’avantage des utilisateurs très prolifiques. Jusqu’à 2 utilisateurs peuvent recevoir le badge par mois. Chaque gagnant reçoit également un message système.

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Tâche planifiée quotidienne (attribue pour le mois civil précédent)

Belle Réponse (J’aime sur une publication)

Les badges Belle Réponse, Bonne Réponse et Grande Réponse suivent tous le même modèle, mais avec un seuil différent pour 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) )

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur agit sur une publication

Beau Partage (Partage de liens)

Les badges Beau Partage, Bon Partage et Grand Partage suivent tous le même modèle, mais avec un seuil différent pour HAVING COUNT(*).

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Beau Sujet (J’aime sur un sujet)

Les badges Beau Sujet, Bon Sujet et Grand Sujet suivent tous le même modèle, mais avec un seuil différent pour 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) )

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur agit sur une publication

Par Amour (J’aime maximum en une journée)

Les badges Par Amour, Amour Supérieur et Fou d’Amour suivent tous le même modèle, mais avec un seuil différent pour HAVING COUNT(*).

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}

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Lien Populaire (Clics sur les liens)

Les badges Lien Populaire, Lien Hot et Lien Célèbre suivent tous le même modèle, mais avec un seuil différent pour tl.clicks.

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Promoteur (Invitations)

Les badges Promoteur, Campagneur et Champion suivent tous le même modèle, mais avec un niveau de confiance différent requis pour les invités et un seuil différent pour HAVING COUNT(*).

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Lire les directives

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lors de la création ou de la modification d’un utilisateur

Lecteur

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur

Merci (J’aime donnés + J’aime reçus)

Les badges Merci, Donne en retour et Empathique suivent tous le même modèle, mais avec des valeurs différentes pour us.likes_given et 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}

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Mise à jour quotidienne

Bienvenue

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur agit sur une publication

Éditeur Wiki

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur modifie ou crée une publication

Discourse Reactions

Première Réaction

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

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur modifie ou crée une publication

Discourse Solved

Résolu !

 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 -- ignorer les sujets résolus par l'auteur original
              AND (:backfill OR p.id IN (:post_ids))
       ) x
  WHERE row_number = 1

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur modifie ou crée une publication

Conseiller en Orientation

Les badges Conseiller en Orientation, Tout-sachant et Institution de Solutions suivent tous le même modèle, mais avec un seuil différent pour HAVING COUNT (*) >= :

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 -- ignorer les sujets résolus par l'auteur original
     AND (:backfill OR p.id IN (:post_ids))
   GROUP BY p.user_id
   HAVING COUNT(*) >= #{min_count}

Exécuter la requête de révocation quotidiennement
La requête cible les publications
Déclencheur	Lorsqu’un utilisateur modifie ou crée une publication

Discourse Github

Je ne peux pas lire ceux-ci.

app/lib/github_badges.rb

Sources :

lib/badge_queries.rb

db/fixtures/001_badges.rb

db/fixtures/001_badges.rb

app/lib/github_badges.rb

[Lilly (Lillian)]

C’est génial @JammyDodger merci d’avoir créé ce sujet utile !