Badge for posts with Likes from a specific group

fefrei · 14.Август.2015 20:44:45

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>

Firepup650 · 24.Август.2023 20:55:07

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

Lilly · 25.Август.2023 00:10:56

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

<НАЗВАНИЕ КАТЕГОРИИ> = Чувствительное к регистру название категории (не 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(*) >= <ПОРОГ КОЛИЧЕСТВА ПОСТОВ>

Firepup650 · 25.Август.2023 01:18:10

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

Lilly · 25.Август.2023 01:21:41

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

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>

Firepup650 · 25.Август.2023 01:26:47

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

Lilly · 25.Август.2023 01:32:40

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

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

Firepup650 · 25.Август.2023 01:38:38

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

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

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

Lilly · 25.Август.2023 02:25:31

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

JammyDodger · 25.Август.2023 05:48:47

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

Lilly · 25.Август.2023 06:40:48

Я сделал это, чтобы получить необходимые таблицы схемы для 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 Лолой

JammyDodger · 25.Август.2023 07:21:52

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

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

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

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

Lilly · 25.Август.2023 07:24:00

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

Lilly · 25.Август.2023 12:50:25

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

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

Firepup650 · 26.Август.2023 02:10:48

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

Lilly · 26.Август.2023 02:27:15

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

JammyDodger · 26.Август.2023 19:27:42

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

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

Firepup650 · 26.Август.2023 19:59:08

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

JammyDodger · 26.Август.2023 21:28:40

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

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

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

Тема		Ответов	Просм.
What is the bug reporter badge? Data & reporting sql-triggered-badge	33	9567	17.09.2023
Badges for posting Data & reporting sql-triggered-badge	4	437	27.08.2024
Badges for specific category Feature	1	1283	13.06.2018
Some common badge queries Data & reporting sql-triggered-badge	0	8839	07.08.2015
Query to create some groups based on activity Data & reporting sql-query	12	872	05.10.2023

Badge for posts with Likes from a specific group

Связанные темы