Значки за количество закрепленных тем / Значки SQL?

Поддержка
1

Наше сообщество ориентировано на закрепленные и глобально закрепленные темы. К сожалению, на страницах профиля пользователя вообще нет счетчика статистики для этого, и, похоже, закрепление/глобальное закрепление не хранится систематически ни в одной части базы данных.

В связи с этим я ищу способ выдачи значков, например, «Первый закрепленный», «10 закрепленных», «100 закрепленных», а также за глобально закрепленные темы.

Не мог бы кто-нибудь помочь мне с SQL-запросом для значков такого типа?

Моя отправная точка:

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.unpinned IS NOT NULL
GROUP BY p.user_id
HAVING count(*) > 5

Но это выдает ошибку:

ERROR: column t.unpinned does not exist
LINE 6: WHERE t.unpinned IS NOT NULL

Думаю, мне придется использовать поле unpinned, так как, насколько мне известно, нет других данных, которые сохранялись бы для тем, когда-либо бывших закрепленными. Или, возможно, я мог бы использовать здесь post_actions?

Или, может быть, искать первый пост в теме от пользователя-администратора или системного пользователя, содержащий «pinned … ago», чтобы вести подсчет? action_code => pinned.disabled

image
image388×150 1.9 KB

Обновление: Похоже, это как-то работает…

SELECT t.user_id, current_timestamp granted_at
FROM badge_posts p
INNER JOIN topics t on t.id = p.topic_id
WHERE p.action_code ilike 'pinned.disabled'
GROUP BY t.user_id
HAVING count(*) > 0

Но я не уверен, что это лучший подход, и так как пост может быть закреплен и откреплен несколько раз… неясно, ловит ли этот запрос только ОДИН случай pinned.disabled на topic.id.

Будет очень признателен за помощь от мастера SQL для значков.

Или же стоит рассмотреть возможность систематического хранения данных о закреплениях в базе данных, подобно «лайкам», и отображать их в разделе статистики профиля пользователя со ссылками «ЗАКРЕПЛЕНО». Ведь если тема закреплена, это выражает гораздо большее признание по сравнению с лайками, и это должно учитываться.

2

Обновление:

Теперь я решил это самостоятельно. Вот окончательный запрос для бейджа:

WITH unpinned AS ( /* Получить темы с хотя бы одним откреплением (pinned.disabled) */
	SELECT t.user_id, t.id
	FROM badge_posts p
	INNER JOIN topics t ON t.id = p.topic_id
	WHERE p.action_code ILIKE 'pinned.disabled'
	GROUP BY t.id, t.user_id
	HAVING count(*) > 0
)
SELECT user_id, count(*), current_timestamp AS granted_at 
FROM unpinned
GROUP BY user_id
HAVING count(*) > 0

Последний подсчёт должен быть изменён на количество тем, которые когда-либо были закреплены, например: >0 — хотя бы одна закреплённая тема, >10 — хотя бы 10 ранее закреплённых тем.

Мне пришлось использовать обходной путь с pinned.disabled, так как в базе данных Discourse нет других доступных данных об истории закрепления тем. Также невозможно различить в этом запросе обычные закрепления и глобальные из-за отсутствия соответствующих данных.

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