Badge for posts with Likes from a specific group

Community Building Data & reporting

AI-generated summary

fefrei proposed a badge idea that grants users a badge when they have at least <POST_COUNT> posts in the category <CATEGORY_NAME> with at least <LIKE_COUNT> likes from users in the group <TEAM_NAME>. Lilly provided a SQL query to implement this badge, which was later modified by JammyDodger to fit the original requirement of granting the badge to users who have X number of posts with Y likes by staff, across all categories.

The final query provided by JammyDodger uses the badge_posts view, which only counts posts from public categories. To include all categories, the query can be modified to use the posts table instead, but this may require additional lines to exclude deleted posts or topics.

Lilly also experimented with using the actual post_action_code_id and group_id code, and developed a query with the help of a SQL assistant (GPT4bot). However, this query still had issues, and JammyDodger eventually provided a revised version that meets the original requirement.

The discussion highlights the complexity of writing badge queries and the importance of testing and refining them to ensure accuracy.

1

As already mentioned here:

Grant a badge to everyone having posted at least <POST_COUNT> posts in the category <CATEGORY_NAME> that have received at least <LIKE_COUNT> likes by users in the group <TEAM_NAME>. Similar to the bug reporter badge here, but can require more than one like.

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
        SELECT id FROM categories WHERE name ilike '<CATEGORY_NAME>'
    ) AND (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<TEAM_NAME>' ) 
            )
    ) >= <LIKE_COUNT>
    AND p.post_number = 1
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST_COUNT>
3

Я немного повозился с этим и почти добился успеха, но не смог применить это ко всем категориям так, как хотел. Есть ли простой способ сделать это?

4

Привет @Firepup650 :slight_smile: Попробуй, возможно, этот вариант. У меня на экземпляре сработал.

<НАЗВАНИЕ КАТЕГОРИИ> = Чувствительное к регистру название категории (не slug)
<ГРУППА> = Название группы (например: Staff, Trust_level_0)
<МИНИМАЛЬНОЕ КОЛИЧЕСТВО ЛАЙКОВ> = минимальное количество лайков, которое вы хотите установить
<ПОРОГ КОЛИЧЕСТВА ПОСТОВ> = минимальное количество постов

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = (
        SELECT id FROM categories WHERE name ilike '<НАЗВАНИЕ КАТЕГОРИИ>'
    ) AND (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<НАЗВАНИЕ ГРУППЫ>' ) 
            )
    ) >= <МИНИМАЛЬНОЕ КОЛИЧЕСТВО ЛАЙКОВ>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <ПОРОГ КОЛИЧЕСТВА ПОСТОВ>

Для нескольких категорий можно сделать так:

<НАЗВАНИЯ КАТЕГОРИЙ> = Чувствительные к регистру названия категорий
<ГРУППА> = Название группы (например: Staff, Trust_level_0)
<МИНИМАЛЬНОЕ КОЛИЧЕСТВО ЛАЙКОВ> = минимальное количество лайков, которое вы хотите установить
<ПОРОГ КОЛИЧЕСТВА ПОСТОВ> = минимальное количество постов

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id IN (
        SELECT id FROM categories WHERE name ILIKE ANY (ARRAY['<НАЗВАНИЕ КАТЕГОРИИ 1>', '<НАЗВАНИЕ КАТЕГОРИИ 2>', '<НАЗВАНИЕ КАТЕГОРИИ 3>'])
    ) AND (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<ГРУППА>' ) 
            )
    ) >= <МИНИМАЛЬНОЕ КОЛИЧЕСТВО ЛАЙКОВ>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <ПОРОГ КОЛИЧЕСТВА ПОСТОВ>
5

Привет, @Lilly!
Оба запроса выглядят отлично, но я хотел бы запустить запрос по всем категориям, если это возможно. Когда я пытался это сделать, постоянно возникали ошибки о том, что подзапрос возвращает несколько строк, поэтому я обратился сюда за помощью.

6

Вы имеете в виду, что хотите использовать один и тот же запрос для всех категорий?

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE '<GROUP>' ) 
            )
    ) >= <MINIMUM LIKE COUNT>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
7

Кажется, это должно сработать, но возникает ошибка, когда группа, против которой вы запускаете это, — staff. Я пробовал использовать как Staff, так и staff в качестве имени группы и временно установил количество постов и лайков в 1, но система сообщает, что значки не будут выданы. Что я делаю не так?

8

Хм, я использовал staff в нижнем регистре, и у меня всё сработало. :thinking:

SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE (
        SELECT count(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
            AND post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
            ) AND pa.user_id IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE gu.group_id = ( SELECT id FROM groups WHERE name ILIKE 'staff' ) 
            )
    ) >= 1
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= 1
9

Странно :face_with_spiral_eyes: У меня всё ещё не работает. Попробую запустить это для нескольких других групп, чтобы понять, в чём проблема.

Редактирование: Запустил для другой группы, и запрос всё равно не сработал. Не понимаю, в чём может быть проблема. Не зависит ли это случайно от основных групп?

Редактирование 2: Это бы не сработало, похоже, staff нельзя устанавливать в качестве основной группы.

10

Думаю, я понял, почему. Я займусь этим после ужина. Мне в любом случае нужна практика SQL. Badge SQL более ограничен по сравнению с PostgreSQL. Я уже справился с частью про подзапросы. :slight_smile:

13

Я ещё не до конца проснулся, и мне нужно как минимум две чашки чая, чтобы полноценно взяться за запросы по бейджам, но я недавно обсуждал с ботом подобные запросы и считаю, что использование реальных post_action_code_id и group_id предпочтительнее, чем вложенные SELECT-запросы для получения того же результата.

14

Я сделал это, чтобы получить необходимые таблицы схемы для posts, posts_actions, group_users и groups

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = '<TABLE NAME>';

Затем использовал этот запрос, чтобы получить все идентификаторы групп:

SELECT name, id FROM groups ORDER BY name

После этого я включил все необходимые таблицы схемы и попросил Лолу, то есть GPTbot, использовать реальный идентификатор post_action_code и код group_id. После некоторых обсуждений и внесения исправлений мы пришли к следующему. Снова кажется, что это работает в Data Explorer, но я всё ещё не могу ничего получить из этого в Badge Previewer.

G = group_id
X = минимальное количество лайков
Y = минимальное количество постов

SELECT pa.user_id, MIN(pa.post_id) as post_id, COUNT(pa.post_id) as post_count, COUNT(pa.id) as like_count, MAX(pa.created_at) as granted_at
FROM post_actions pa
JOIN group_users gu ON gu.user_id = pa.user_id
WHERE gu.group_id = G AND pa.post_action_type_id = 2
GROUP BY pa.user_id
HAVING COUNT(pa.post_id) >= Y AND COUNT(pa.id) >= X

Да, я назвал GP4bot Лолой

15

Я назвал своего Берт. :slight_smile: Хотя у нас сложные отношения.

Думаю, ещё одно ограничение такого типа запроса: использование MIN(p.created_at) granted_at возвращает дату его первого, а не, скажем, десятого. Можно изменить на MAX, но это тоже даст «неверную» дату, если выполнить запрос по историческим данным, где у него уже было больше 10.

Я всё ещё обдумываю этот момент.

У меня был некоторый успех с использованием ROW_NUMBER(), но пока ничего конкретного.

16

Да, я согласен. Что-то всё ещё кажется неправильным. Я иду спать. :sweat_smile:

18

Мне это нравится, и это помогает мне заново изучить SQL и научиться писать более качественные запросы. Lola / GPT4bot как помощник по SQL очень полезна, но её нужно направлять и задавать вопросы правильно. Я пытался найти способ предоставить ей доступ к большей части информации о схемах таблиц, чтобы не пришлось описывать её для каждой задачи по запросам. Предоставление информации о схеме таблиц даёт гораздо лучшие результаты. Я пробовал дать ей ссылку на доступные схемы в core, но это заставило её просто бродить по Google.

Мне интересно работать с ней, когда я буду знать, что предпросмотр запросов на значки работает. Мне нужно практиковаться в SQL и в запросах на значки. Кстати, она не может это исправить, и мой Earl Grey у неё всё ещё получается недостаточно горячим. Хотя вчерашний урок по SQL был лучшим свиданием за последние годы. :facepalm:

20

При использовании этого запроса у нас возникла странная проблема. Похоже, награды получили только сотрудники, хотя я почти уверен, что некоторые не-сотрудники тоже подходят под эти критерии. Это я где-то что-то сломал или проблема в самом запросе?

21

Да, я знаю, что что-то не так. Я займусь этим, как только мой инстанс обновится с исправлением предпросмотра.

22

Давайте начнём с чистого листа, так как, думаю, эти обсуждения меня запутали. :slight_smile:

Цель в том, чтобы выдавать значок за определённое количество постов во всех категориях, которые хотя бы раз лайкнул @staff?

23

Я планировал выдавать это пользователям, у которых есть X сообщений с Y лайками от сотрудников во всех категориях. В моём случае — 10 сообщений и 5 лайков.

25

После запутанного сбоя, вызванного удалёнными лайками, которые сбили мои тесты, я считаю, что это переработанная версия той, что в исходном посте, и она соответствует вашим критериям: :slight_smile:

SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p
WHERE (SELECT COUNT(*)
        FROM post_actions pa
        WHERE pa.post_id = p.id
         AND post_action_type_id = 2 
         AND deleted_at IS NULL
         AND pa.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 3)
       ) >= 5
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10

staff love
staff love1251×405 16.4 KB

Она работает на основе представления badge_posts, поэтому учитывает только посты из публичных категорий, что, возможно, стоит учесть в зависимости от настроек вашего форума и категорий. Также использование CURRENT_TIMESTAMP для поля granted_at — ещё один вариант, но это, скорее, вопрос личных предпочтений.