Le query badge predefinite

JammyDodger · 27 Agosto 2023, 9:09am

Questa è una guida di riferimento per le query SQL dei badge predefiniti e per le relative informazioni di attivazione (ove disponibili).

Badge Core

Anniversario

(Questo badge include una logica aggiuntiva nel backend per la selezione delle date, ma lo includiamo comunque)

 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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Apprezzato (numero di Mi piace su più post)

I badge Apprezzato, Rispettato e Ammirato seguono lo stesso schema, ma con valori diversi per p.like_count e 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}


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Autobiografo

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente viene modificato o creato

Base (Livelli di fiducia)

I badge Base, Membro, Regolare e Leader seguono tutti lo stesso schema, ma con un valore diverso per trust_level.

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente cambia livello di fiducia

Certificato e Abilitato

Questi badge non hanno una query SQL. Fanno parte di Discourse Narrative Bot e vengono assegnati programmaticamente quando un utente completa i tutorial interattivi (Discobot).

Editore

 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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente modifica o crea un post

Appassionato

I badge Appassionato, Aficionado e Devoto seguono lo stesso schema, ma con una soglia diversa per 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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Primo Emoji

Non esiste una query SQL: il badge viene assegnato non appena un post qualificante viene elaborato tramite CookedPostProcessor#grant_badges.

Criterio: Il post elaborato deve contenere almeno un elemento img.emoji che non sia all’interno di un blocco aside.quote. In altre parole, contano gli emoji digitati direttamente nel corpo del post; gli emoji che appaiono solo all’interno di una citazione non contano.


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Elaborazione del post (CookedPostProcessor)

Prima Segnalazione

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente compie un’azione su un post

Primo Mi piace

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente compie un’azione su un post

Primo 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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente modifica o crea un post

Primo Menzione

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente modifica o crea un post

Primo Onebox

Non esiste una query SQL. Viene assegnato durante l’elaborazione del post tramite CookedPostProcessor#grant_badges.

Criterio: Il post elaborato deve aver generato almeno un onebox: un’anteprima di link ricca generata quando un URL nudo e crudo su una riga propria viene risolto in una risorsa incorporabile.


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Elaborazione del post (`CookedPostProcessor`)

Prima Citazione

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente modifica o crea un post

Prima Risposta via Email

Non esiste una query SQL. Viene assegnato durante l’elaborazione del post tramite CookedPostProcessor#grant_badges.

Criterio: post.is_reply_by_email? deve essere true, ovvero il post è stato inviato rispondendo a un’email di notifica di Discourse anziché tramite l’interfaccia web. È necessario che la funzionalità di risposta alle email in arrivo sia abilitata sul sito.


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Elaborazione del post (`CookedPostProcessor`)

Suggerimento originale della community 'Reply by email' badge - #3 by lrossouw

Primo Condivisione

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Nuovo Utente del Mese

Non esiste una query SQL registrata per il badge. Viene assegnato da un job schedulato giornaliero (Jobs::GrantNewUserOfTheMonthBadges). Il job viene eseguito ogni giorno, ma assegna il badge solo per il mese di calendario precedente e una sola volta al mese.

Requisiti: Il candidato deve aver creato il proprio account durante il mese di calendario precedente, essere attivo e non in fase di staging, non essere amministratore o moderatore e non essere sospeso. Deve inoltre aver pubblicato in almeno 2 topic distinti con almeno 2 post in totale e aver ricevuto almeno 2 mi piace.

Punteggio: I candidati sono classificati in base a un punteggio ponderato basato sui mi piace. Ogni mi piace ricevuto è ponderato in base al livello di fiducia della persona che lo ha assegnato:

Chi assegna il Mi piace	Peso
Amministratore o Moderatore	3.0
Livello di fiducia 4	2.0
Livello di fiducia 3	1.5
Livello di fiducia 2	1.0
Livello di fiducia 1	0.25
Livello di fiducia 0	0.1

Il punteggio finale è SUM(mi piace ponderati) / (5 + numero di post): dividere per il numero di post riduce il vantaggio degli utenti molto prolifici. Fino a 2 utenti possono ricevere il badge al mese. Ogni vincitore riceve anche un messaggio di sistema.


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Job schedulato giornaliero (assegna per il mese di calendario precedente)

Bella Risposta (Mi piace su un post)

I badge Bella Risposta, Buona Risposta e Ottima Risposta seguono tutti lo stesso schema, ma con una soglia diversa per 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) )


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente compie un’azione su un post

Bella Condivisione (Condivisione di link)

I badge Bella Condivisione, Buona Condivisione e Ottima Condivisione seguono tutti lo stesso schema, ma con una soglia diversa per 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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Bella Discussione (Mi piace su un topic)

I badge Bella Discussione, Buona Discussione e Ottima Discussione seguono tutti lo stesso schema, ma con una soglia diversa per 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) )


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente compie un’azione su un post

Per Amore (Massimo di mi piace in un giorno)

I badge Per Amore, Amore Maggiore e Pazzo per Amore seguono tutti lo stesso schema, ma con una soglia diversa per 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}


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Link Popolare (Clic sui link)

I badge Link Popolare, Link Caldo e Link Famoso seguono tutti lo stesso schema, ma con una soglia diversa per 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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Promotore (Inviti)

I badge Promotore, Campionatore e Campione seguono tutti lo stesso schema, ma con un valore diverso di Livello di Fiducia richiesto per gli invitati e una soglia diversa per 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) )


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Leggi le Linee Guida

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente viene modificato o creato

Lettore

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger

Grazie (Mi piace dati + Mi piace ricevuti)

I badge Grazie, Restituisce il favore e Empatico seguono tutti lo stesso schema, ma con valori diversi per us.likes_given e 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}


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Aggiornamento quotidiano

Benvenuto

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente compie un’azione su un post

Editore 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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente modifica o crea un post

Discourse Reactions

Prima Reazione

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


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente modifica o crea un post

Discourse Solved

Risolto!

 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 -- ignora i topic risolti dall'OP
              AND (:backfill OR p.id IN (:post_ids))
       ) x
  WHERE row_number = 1


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente modifica o crea un post

Orientatore

I badge per Orientatore, Saputello e Istituzione delle Soluzioni seguono tutti lo stesso schema, ma con una soglia diversa per 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 -- ignora i topic risolti dall'OP
     AND (:backfill OR p.id IN (:post_ids))
   GROUP BY p.user_id
   HAVING COUNT(*) >= #{min_count}


Esegui query di revoca quotidianamente
La query mira ai post
Trigger	Quando un utente modifica o crea un post

Discourse Github

Non riesco a leggere questi.

github.com/discourse/discourse-github