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>
J’ai bidouillé ça et j’ai failli y arriver, mais je n’ai pas réussi à l’appliquer à toutes les catégories comme je le voulais. Y a-t-il un moyen simple de le faire ?
Salut @Firepup650 peut-être essayer celui-ci. il a fonctionné sur mon instance.
<NOM DE CATÉGORIE> = Nom de catégorie sensible à la casse (pas le slug)
<GROUPE> = Nom du groupe (par exemple : Staff, Trust_level_0)
<NOMBRE MINIMUM DE LIKES> = nombre minimum de likes que vous souhaitez définir
<SEUIL DE NOMBRE DE MESSAGES> = nombre minimum de messages
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 '<NOM DE CATÉGORIE>'
) 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 '<NOM DE GROUPE>' )
)
) >= <NOMBRE MINIMUM DE LIKES>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <SEUIL DE NOMBRE DE MESSAGES>
pour plusieurs catégories, vous pouvez faire ceci :
<NOMS DE CATÉGORIES> = Noms de catégories sensibles à la casse
<GROUPE> = Nom du groupe (par exemple : Staff, Trust_level_0)
<NOMBRE MINIMUM DE LIKES> = nombre minimum de likes que vous souhaitez définir
<SEUIL DE NOMBRE DE MESSAGES> = nombre minimum de messages
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['<NOM DE CATÉGORIE 1>', '<NOM DE CATÉGORIE 2>', '<NOM DE CATÉGORIE 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 '<GROUPE>' )
)
) >= <NOMBRE MINIMUM DE LIKES>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <SEUIL DE NOMBRE DE MESSAGES>
Ces deux requêtes semblent excellentes, mais j’aimerais juste exécuter la requête sur toutes les catégories si possible. Quand j’ai essayé, j’ai continué à recevoir des erreurs concernant une sous-requête retournant plusieurs lignes, alors je suis venu demander de l’aide ici.
Voulez-vous dire que vous souhaitez la même requête pour toutes les catégories ?
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>
Cela semble fonctionner, mais semble échouer lorsque le groupe que vous exécutez est staff. J’ai essayé Staff et staff comme nom de groupe, et j’ai temporairement défini le nombre de publications et de j’aime sur 1, et il indique qu’aucun badge ne serait accordé. Qu’est-ce que je fais mal ici ?
hmm j’ai utilisé le personnel en minuscules et cela a fonctionné pour moi.
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
Bizarre Ça ne fonctionne toujours pas pour moi. Je vais essayer de l’exécuter sur quelques autres groupes pour voir si je peux trouver le problème.
Edit : J’ai exécuté sur un groupe différent, et la requête a toujours échoué. Je ne suis pas sûr de ce que pourrait être le problème ici. Cela dépend-il des groupes principaux par hasard ?
Edit 2 : Cela ne fonctionnerait pas, staff ne semble pas pouvoir être défini comme groupe principal.
Je pense savoir pourquoi. Je vais travailler là-dessus pour vous après avoir dîné. J’ai besoin de pratiquer SQL de toute façon. Badge SQL est plus restrictif que postgres. J’ai dépassé la partie sous-requête.
Je ne me suis pas encore tout à fait réveillé, et je trouve que les requêtes de badges nécessitent deux bonnes tasses de thé avant de pouvoir les aborder pleinement, mais j’ai récemment discuté avec le bot de ce genre de requêtes, et je pense qu’utiliser le post_action_code_id et le group_id réels est mieux que d’utiliser des requêtes SELECT imbriquées pour trouver la même chose.
J’ai fait cela pour obtenir les tables de schéma nécessaires pour posts, posts_actions, group_users et groups
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';
Ensuite, j’ai utilisé ceci pour obtenir tous les identifiants de groupe :
SELECT name, id FROM groups ORDER BY name
J’ai donc inclus toutes les tables de schéma nécessaires et demandé à Lola, euh GPTbot, d’utiliser l’identifiant post_action_code réel et le code group_id. Puis, après quelques discussions et corrections, nous sommes arrivés à ceci. Encore une fois, cela semble fonctionner dans Data Explorer, mais je n’arrive toujours rien à en tirer dans l’aperçu des badges.
G = group_id
X = nombre minimum de likes
Y = nombre minimum 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
Je l’appelle Bert. Bien que nous ayons une relation compliquée.
Je pense qu’une autre limite à ce type de requête est l’utilisation de MIN(p.created_at) granted_at qui me donne la date de leur première et non, disons, la date de leur 10ème. Cela pourrait être changé en MAX, mais cela donnerait également une date “incorrecte” si vous l’exécutiez sur des données historiques où ils en avaient déjà plus de 10.
J’y réfléchis encore.
J’ai eu du succès en utilisant ROW_NUMBER(), mais rien de concret pour le moment.
Je m’amuse bien avec ça et cela m’aide à réapprendre le SQL et à écrire de meilleures requêtes. Avoir Lola / GPT4bot comme assistant SQL est utile, mais il faut la guider et lui poser les bonnes questions. J’essaie de trouver des moyens de lui donner accès à la plupart des informations des tables du schéma afin de ne pas avoir à le faire pour chaque problème de requête sur lequel nous travaillons. Fournir les informations du schéma de table donne de bien meilleurs résultats. J’ai essayé de lui donner un lien vers les schémas disponibles dans le cœur, mais cela l’a juste fait chercher sur Google.
Je suis intéressé à travailler avec elle quand je sais que l’aperçu des requêtes de badge fonctionne. J’ai besoin de pratiquer le SQL et de faire des requêtes de badge. Soit dit en passant, elle ne peut pas le réparer et elle fait toujours mon thé Earl Grey pas assez chaud. Bien que la leçon de SQL d’hier soir ait été le meilleur rendez-vous que j’ai eu depuis des années.
Avec cette requête, nous semblons avoir eu un problème étrange. Elle semble avoir récompensé uniquement le personnel, et je suis presque certain que certains non-membres du personnel rempliraient ce critère. Est-ce quelque chose que j’ai cassé quelque part, ou est-ce un problème de requête ?
Je voulais que cela soit accordé aux utilisateurs qui ont X nombre de publications avec Y likes par le personnel, dans toutes les catégories. Dans mon cas, 10 publications, 5 likes.
Après un petit problème avec la suppression de Likes qui a faussé mes tests, je pense que voici une version révisée de celle dans le OP qui correspond à vos critères :
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
Elle fonctionne à partir de la vue badge_posts, donc elle ne compte que les publications des catégories publiques, ce que vous voudrez peut-être prendre en considération en fonction de la configuration de votre forum/catégories. De plus, utiliser CURRENT_TIMESTAMP pour granted_at est une autre option, mais c’est probablement une question de goût.
peut-être essayer celui-ci. il a fonctionné sur mon instance.
Ça ne fonctionne toujours pas pour moi. Je vais essayer de l’exécuter sur quelques autres groupes pour voir si je peux trouver le problème.
