Запросы по умолчанию для значков

[JammyDodger]

Это справочное руководство по SQL-запросам для значков по умолчанию и их триггерной информации (где она доступна).

Основные значки

Юбилей

(В этом значке используется дополнительная магия бэкенда для выбора дат, но я всё же включу его)

 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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Оценённый (количество лайков на нескольких постах)

Значки Оценённый, Уважаемый и Восхищённый следуют одному и тому же шаблону, но с разными значениями для p.like_count и 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}

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Автобиограф

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании пользователя

Базовый (Уровни доверия)

Значки Базовый, Участник, Регулярный, Лидер следуют одному и тому же шаблону, но с другим значением 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)
      )

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При изменении уровня доверия пользователя

Сертифицированный и Лицензированный

Для этих значков нет SQL-запроса. Они являются частью Discourse Narrative Bot и выдаются программно, когда пользователь завершает интерактивные учебные курсы (Discobot).

Редактор

 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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании поста пользователем

Энтузиаст

Значки Энтузиаст, Афицио и Преданный следуют одному и тому же шаблону, но с разным пороговым значением 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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Первый эмодзи

Нет SQL-запроса — значок выдаётся в момент обработки подходящего поста через CookedPostProcessor#grant_badges.

Критерий: Обработанный пост должен содержать хотя бы один элемент img.emoji, который не находится внутри блока aside.quote. Иными словами, учитываются эмодзи, введённые непосредственно в теле поста; эмодзи, появляющиеся только внутри цитируемого фрагмента, не учитываются.

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обработка поста (CookedPostProcessor)

Первый флаг

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При действии пользователя над постом

Первый лайк

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При действии пользователя над постом

Первая ссылка

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании поста пользователем

Первое упоминание

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании поста пользователем

Первый onebox

Нет SQL-запроса. Выдаётся во время обработки поста через CookedPostProcessor#grant_badges.

Критерий: Обработанный пост должен содержать хотя бы один onebox — расширенную превью-ссылку, генерируемую, когда URL, находящийся на отдельной строке, разрешается во встраиваемый ресурс.

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обработка поста (CookedPostProcessor)

Первая цитата

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании поста пользователем

Первый ответ по электронной почте

Нет SQL-запроса. Выдаётся во время обработки поста через CookedPostProcessor#grant_badges.

Критерий: post.is_reply_by_email? должно быть true — то есть пост был отправлен в ответ на уведомление Discourse по электронной почте, а не через веб-интерфейс. Требуется включение функции входящих ответов по электронной почте на сайте.

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обработка поста (CookedPostProcessor)

Оригинальное предложение сообщества 'Reply by email' badge - #3 by lrossouw

Первый шеринг

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Новый пользователь месяца

Для этого значка не зарегистрирован SQL-запрос. Выдаётся ежедневным запланированным заданием (Jobs::GrantNewUserOfTheMonthBadges). Задание выполняется каждый день, но награда выдаётся только за предыдущий календарный месяц и только один раз в месяц.

Критерии участия: Кандидат должен создать учётную запись в течение предыдущего календарного месяца, быть активным и не являться тестовым пользователем, не быть администратором или модератором, а также не быть заблокированным. Также необходимо, чтобы пользователь опубликовал посты как минимум в 2 разных темах, всего не менее 2 постов, и получил как минимум 2 лайка.

Оценка: Кандидаты ранжируются по взвешенному баллу за лайки. Каждый полученный лайк взвешивается в зависимости от уровня доверия пользователя, поставившего лайк:

Поставивший лайк	Вес
Администратор или модератор	3.0
Уровень доверия 4	2.0
Уровень доверия 3	1.5
Уровень доверия 2	1.0
Уровень доверия 1	0.25
Уровень доверия 0	0.1

Итоговый балл рассчитывается как SUM(взвешенные лайки) / (5 + количество постов) — деление на количество постов снижает преимущество активных авторов. Значок может быть выдан до 2 пользователям в месяц. Каждый победитель также получает системное сообщение.

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Ежедневное запланированное задание (награда за предыдущий календарный месяц)

Хороший ответ (Лайки на посте)

Значки Хороший ответ, Хороший ответ и Великолепный ответ следуют одному и тому же шаблону, но с разным пороговым значением для 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) )

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При действии пользователя над постом

Хороший шеринг (Обмен ссылками)

Значки Хороший шеринг, Хороший шеринг и Великолепный шеринг следуют одному и тому же шаблону, но с разным пороговым значением 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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Хорошая тема (Лайки на теме)

Значки Хорошая тема, Хорошая тема и Великолепная тема следуют одному и тому же шаблону, но с разным пороговым значением для 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) )

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При действии пользователя над постом

От любви (Максимум лайков за день)

Значки От любви, Высшая любовь и Безумно влюблённый следуют одному и тому же шаблону, но с разным пороговым значением 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}

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Популярная ссылка (Клики по ссылкам)

Значки Популярная ссылка, Горячая ссылка и Знаменитая ссылка следуют одному и тому же шаблону, но с разным пороговым значением 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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Продвижение (Приглашения)

Значки Продвижение, Кампания и Чемпион следуют одному и тому же шаблону, но с разным значением уровня доверия, требуемым для приглашённых, и разным пороговым значением 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) )

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Прочитал правила

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании пользователя

Читатель

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер

Спасибо (Данные лайки + Полученные лайки)

Значки Спасибо, Возвращает добро и Сочувствующий следуют одному и тому же шаблону, но с разными значениями для us.likes_given и 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}

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	Обновление ежедневно

Приветствие

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При действии пользователя над постом

Редактор вики

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании поста пользователем

Discourse Reactions

Первая реакция

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

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании поста пользователем

Discourse Solved

Решено!

 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 -- игнорировать темы, решённые автором (OP)
              AND (:backfill OR p.id IN (:post_ids))
       ) x
  WHERE row_number = 1

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании поста пользователем

Консультант по вопросам

Значки Консультант по вопросам, Всезнайка и Учреждение решений следуют одному и тому же шаблону, но с разным пороговым значением 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 -- игнорировать темы, решённые автором (OP)
     AND (:backfill OR p.id IN (:post_ids))
   GROUP BY p.user_id
   HAVING COUNT(*) >= #{min_count}

Запускать запрос на отзыв ежедневно
Запрос нацелен на посты
Триггер	При редактировании или создании поста пользователем

Discourse Github

Я не могу прочитать эти.

app/lib/github_badges.rb

Источники:

lib/badge_queries.rb

db/fixtures/001_badges.rb

db/fixtures/001_badges.rb

app/lib/github_badges.rb

[Lilly (Lillian)]

Это здорово, @JammyDodger, спасибо за создание этой полезной темы!