Badges para número de tópicos fixados / Badge SQL?

Suporte
1

Nossa comunidade é centrada em tópicos fixados e fixados globalmente. Infelizmente, não há nenhuma contagem de estatísticas para isso nas páginas de perfil do usuário, e parece que os tópicos fixados/fixados globalmente não são armazenados de forma sistemática em lugar algum no banco de dados.

Diante disso, estou procurando uma maneira de conceder pelo menos os seguintes emblemas: “Primeiro fixado”, “10 fixados”, “100 fixados”, além de tópicos fixados globalmente.

Alguém poderia me ajudar com o SQL para esses tipos de emblemas?

Meu ponto de partida:

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

Mas isso me dá o erro:

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

Acho que terei que usar unpinned, já que, até onde sei, não há nenhum outro ponto de dados que permaneça associado a tópicos que já foram fixados. Ou eu poderia usar post_actions aqui?

Ou talvez pesquisar pela primeira postagem em um tópico feita por um usuário de equipe/sistema que contenha “pinned … ago” para contar? action_code => pinned.disabled

image
image388×150 1.9 KB

Atualização: Isso parece funcionar de alguma forma…

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

Mas não tenho certeza se essa é a melhor abordagem e, como uma postagem pode ser fixada e desfixada várias vezes… não sei se essa consulta captura apenas UM pinned.disabled por topic.id.

A ajuda de um especialista em SQL para emblemas seria muito apreciada.

Ou também a consideração de armazenar fixações de forma sistemática no banco de dados, como “likes” etc., daqui para frente, e exibi-las na seção de Estatísticas do perfil do usuário, abaixo, com links como “FIXADO”. Porque, se um tópico for fixado, é um reconhecimento muito maior em comparação com likes e deveria ser contabilizado.

2

Atualização:

Resolvi isso sozinho agora. Aqui está a consulta final do emblema:

WITH unpinned AS ( /* Obter tópicos elegíveis com pelo menos um 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 granted_at 
FROM unpinned
GROUP BY user_id
HAVING count(*) > 0

A última contagem deve ser alterada para o número de tópicos que já foram fixados, por exemplo: >0 significa pelo menos um tópico fixado, >10 significa pelo menos 10 tópicos que já foram fixados.

Fui obrigado a usar a solução alternativa de pinned.disabled, pois o Discourse não possui nenhum outro ponto de dados sobre históricos de fixação disponível no banco de dados. Além disso, não é possível distinguir essa consulta entre fixações regulares e fixações globais devido à falta de dados disponíveis.

Isso deveria ser alterado no futuro, na minha opinião, pois fixação global > fixação regular > comentários > curtidas > visualizações para estatísticas de engajamento, mas eles não são considerados de forma alguma.