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>
Eu estava mexendo nisso e quase consegui fazer funcionar, mas não consegui aplicá-lo a todas as categorias da maneira que eu queria. Existe uma maneira simples de fazer isso?
Olá @Firepup650 talvez tente este. funcionou na minha instância.
<NOME DA CATEGORIA> = Nome da categoria sensível a maiúsculas e minúsculas (não slug)
<GRUPO> = Nome do Grupo (ex: Staff, Trust_level_0)
<CONTADOR MÍNIMO DE CURTIDAS> = número mínimo de curtidas que você deseja definir
<LIMITE DE CONTADOR DE POSTAGENS> = número mínimo de postagens
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 '<NOME DA CATEGORIA>'
) 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 '<NOME DO GRUPO>' )
)
) >= <CONTADOR MÍNIMO DE CURTIDAS>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <LIMITE DE CONTADOR DE POSTAGENS>
para múltiplas categorias você pode fazer isso:
<NOMES DAS CATEGORIAS> = Nomes das categorias sensíveis a maiúsculas e minúsculas
<GRUPO> = Nome do Grupo (ex: Staff, Trust_level_0)
<CONTADOR MÍNIMO DE CURTIDAS> = número mínimo de curtidas que você deseja definir
<LIMITE DE CONTADOR DE POSTAGENS> = número mínimo de postagens
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['<NOME DA CATEGORIA 1>', '<NOME DA CATEGORIA 2>', '<NOME DA CATEGORIA 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 '<GRUPO>' )
)
) >= <CONTADOR MÍNIMO DE CURTIDAS>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <LIMITE DE CONTADOR DE POSTAGENS>
Ambas parecem ótimas consultas, mas eu queria apenas executar a consulta em todas as categorias, se possível. Quando tentei, fiquei recebendo erros sobre uma subconsulta retornando várias linhas, então vim perguntar aqui sobre isso.
Você quer dizer que deseja a mesma consulta para todas as categorias?
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>
Isso parece que funcionaria, mas falha quando o grupo contra o qual você está executando é staff. Tentei Staff e staff como nome do grupo, e temporariamente defini a contagem de posts e curtidas para 1, e diz que nenhum distintivo seria concedido. O que estou fazendo de errado aqui?
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
Estranho Ainda não funciona para mim. Tentarei executá-lo em alguns outros grupos para ver se consigo encontrar o problema.
Editar: Executei em um grupo diferente e a consulta ainda falhou. Não tenho certeza de qual seria o problema aqui. Depende de grupos primários por acaso?
Editar 2: Isso não funcionaria, staff não parece ter permissão para ser definido como um grupo primário.
Acho que sei o motivo. Vou trabalhar nisso para você depois de jantar. Preciso de prática em SQL de qualquer maneira. SQL de distintivo é mais restritivo que postgres. Passei da parte da subconsulta.
Ainda não acordei completamente, e acho que as consultas de distintivos precisam de duas xícaras de chá para serem totalmente resolvidas, mas tenho conversado com o bot recentemente sobre esse tipo de consulta e acho que usar o post_action_code_id e o group_id reais é melhor do que usar as consultas SELECT aninhadas para encontrar a mesma coisa.
Eu fiz isso para obter as tabelas de esquema necessárias para posts, posts_actions, group_users e groups
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';
Em seguida, usei isso para obter todos os IDs de grupo:
SELECT name, id FROM groups ORDER BY name
Então incluí todas as tabelas de esquema necessárias e instruí Lola, er GPTbot, a usar o ID real de post_action_code e o código group_id. Então, após alguma discussão e correções. Chegamos a isso. Novamente, parece funcionar no Explorador de Dados, mas ainda não consigo obter nada dele no Visualizador de Emblemas.
G = group_id
X = número mínimo de curtidas
Y = número mínimo de posts
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
Eu chamo o meu de Bert. Embora tenhamos um relacionamento complicado.
Acho que outra limitação para esse tipo de consulta é usar MIN(p.created_at) granted_at que me dá a data da primeira e não, digamos, a data da décima. Poderia ser alterado para MAX, mas isso também daria uma data ‘errada’ se você executasse contra dados históricos onde eles já tinham mais de 10.
Ainda estou pensando nisso.
Tive algum sucesso usando ROW_NUMBER(), mas nada concreto ainda.
Estou me divertindo com isso e está me ajudando a reaprender SQL e como escrever consultas melhores. Ter Lola / GPT4bot como assistente SQL é útil, mas você tem que guiá-la e perguntar da maneira certa. Estou tentando descobrir maneiras de dar a ela acesso à maior parte das informações das tabelas do esquema para que eu não precise fazer isso para cada problema de consulta em que trabalhamos. Fornecer as informações do esquema da tabela dá resultados muito melhores. Tentei dar a ela um link para os esquemas disponíveis no core, mas isso apenas a fez perder tempo no Google.
Estou interessado em trabalhar com ela quando souber que o previewer de consulta de badge está funcionando. Preciso praticar SQL e fazer consultas de badge. Aliás, ela não consegue consertar e ainda faz meu chá Earl Grey não ficar quente o suficiente. Embora a aula de SQL de ontem à noite tenha sido o melhor encontro que tive em anos.
Com essa consulta, parece que tivemos um problema estranho. Parece que foi concedido apenas à equipe, e tenho quase certeza de que alguns não funcionários atenderiam a esse critério. Isso é algo que quebrei em algum lugar, ou é um problema de consulta?
Eu pretendia que fosse concedido a usuários que tivessem X número de posts com Y curtidas pela equipe, em todas as categorias. No meu caso, 10 posts, 5 curtidas.
Depois de um pequeno contratempo com alguns Likes excluídos que atrapalharam meus testes, acho que esta é uma versão revisada daquela no OP que atende aos seus critérios:
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
Ele funciona a partir da visualização badge_posts, então ele só conta postagens de categorias públicas, o que você pode querer levar em consideração dependendo da configuração do seu fórum/categoria. Além disso, usar CURRENT_TIMESTAMP para granted_at é outra opção, mas provavelmente é uma questão de gosto.
talvez tente este. funcionou na minha instância.
Ainda não funciona para mim. Tentarei executá-lo em alguns outros grupos para ver se consigo encontrar o problema.
