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>
Ciao @Firepup650 forse prova questo. ha funzionato sulla mia istanza.
<NOME CATEGORIA> = Nome categoria sensibile alle maiuscole (non slug)
<GRUPPO> = Nome gruppo (es: Staff, Trust_level_0)
<NUMERO MINIMO DI LIKE> = numero minimo di like che vuoi impostare
<SOGLIA NUMERO POST> = numero minimo di post
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 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 GRUPPO>' )
)
) >= <NUMERO MINIMO DI LIKE>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <SOGLIA NUMERO POST>
per più categorie puoi fare così:
<NOMI CATEGORIE> = Nomi delle categorie sensibili alle maiuscole
<GRUPPO> = Nome gruppo (es: Staff, Trust_level_0)
<NUMERO MINIMO DI LIKE> = numero minimo di like che vuoi impostare
<SOGLIA NUMERO POST> = numero minimo di post
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 CATEGORIA 1>', '<NOME CATEGORIA 2>', '<NOME 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 '<GRUPPO>' )
)
) >= <NUMERO MINIMO DI LIKE>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <SOGLIA NUMERO POST>
Entrambe sembrano ottime query, ma volevo solo eseguire la query su tutte le categorie, se possibile. Quando ho provato, ho continuato a ricevere errori riguardo a una sottoquery che restituiva più righe, quindi sono venuto qui a chiedere.
Intendi che desideri la stessa query per tutte le categorie?
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>
Sembra che funzionerebbe, ma sembra fallire quando il gruppo contro cui lo stai eseguendo è staff. Ho provato sia Staff che staff come nome del gruppo, e ho temporaneamente impostato il conteggio dei post e dei like a 1, e dice che non verranno concesse badge. Cosa sto sbagliando qui?
hmm ho usato staff in minuscolo e ha funzionato per me.
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
Strano Ancora non funziona per me. Proverò ad eseguirlo su altri gruppi per vedere se riesco a trovare il problema.
Modifica: Eseguito su un gruppo diverso e la query è ancora fallita. Non sono sicuro di quale sia il problema qui. Dipende per caso dai gruppi primari?
Modifica 2: Non funzionerebbe, staff non sembra poter essere impostato come gruppo primario.
Penso di sapere perché. Ci lavorerò per te dopo aver cenato. Ho comunque bisogno di fare pratica con SQL. Badge SQL è più restrittivo di postgres. Sono andato oltre la parte della sottoquery.
Non mi sono ancora svegliato del tutto e mi rendo conto che le query sui badge richiedono un solido paio di tazze di tè prima di poterle affrontare appieno, ma ho parlato di recente con il bot riguardo a questo tipo di query e penso che utilizzare il vero post_action_code_id e il group_id siano meglio che utilizzare le query SELECT nidificate per trovare la stessa cosa.
Ho fatto questo per ottenere le tabelle di schema necessarie per posts, posts_actions, group_users e groups
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';
Poi ho usato questo per ottenere tutti gli ID dei gruppi:
SELECT name, id FROM groups ORDER BY name
Quindi ho incluso tutte le tabelle di schema necessarie e ho istruito Lola, ehm GPTbot, a usare l’ID effettivo di post_action_code e il codice group_id. Poi, dopo qualche discussione e alcune correzioni, siamo arrivati a questo. Ancora, sembra funzionare in Data Explorer, ma non riesco ancora a ottenere nulla dall’Anteprima Badge.
G = group_id
X = numero minimo di like
Y = numero minimo di post
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
Lo chiamo Bert. Anche se abbiamo una relazione complicata.
Penso che un’altra limitazione a quel tipo di query sia l’uso di MIN(p.created_at) granted_at che mi dà la data del loro primo e non, diciamo, la data del loro decimo. Potrebbe essere cambiato in MAX, ma darebbe anche una data ‘sbagliata’ se la eseguissi su dati storici in cui ne avevano già più di 10.
Ci sto ancora pensando.
Ho avuto un po’ di successo usando ROW_NUMBER(), ma niente di concreto ancora.
Mi sto divertendo con questo e mi sta aiutando a riapprendere SQL e come scrivere query migliori. Avere Lola / GPT4bot come assistente SQL è utile, ma devi guidarla e chiederle nel modo giusto. Ho cercato di capire come darle accesso alla maggior parte delle informazioni sulle tabelle dello schema in modo da non doverlo fare per ogni problema di query su cui lavoriamo. Fornire le informazioni sullo schema della tabella dà risultati molto migliori. Ho provato a darle un link agli schemi disponibili in core, ma questo l’ha solo fatta andare a cercare su Google.
Sono interessato a lavorare con lei quando saprò che il badge query previewer funziona. Ho bisogno di fare pratica con SQL e con le badge query. Tra l’altro, lei non può sistemarlo e mi fa ancora il mio tè Earl Grey non abbastanza caldo. Anche se la lezione di SQL di ieri sera è stata il miglior appuntamento che ho avuto da anni.
Usando quella query, sembra che abbiamo avuto un problema strano. Sembra che sia stato assegnato solo allo staff e sono quasi certo che alcuni non staff soddisferebbero quel criterio. È qualcosa che ho rotto da qualche parte, o è un problema di query?
Intendevo che fosse concesso agli utenti che hanno X numero di post con Y like da parte dello staff, in tutte le categorie. Nel mio caso, 10 post, 5 like.
Dopo un confuso intoppo con alcuni Mi piace eliminati che hanno sballato i miei test, penso che questa sia una versione rivista di quella nell’OP che soddisfa i tuoi criteri:
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
Funziona sulla vista badge_posts, quindi conta solo i post dalle categorie pubbliche, cosa che potresti voler prendere in considerazione a seconda della configurazione del tuo forum/delle tue categorie. Inoltre, usare CURRENT_TIMESTAMP per granted_at è un’altra opzione, ma probabilmente è una questione di gusti.
forse prova questo. ha funzionato sulla mia istanza.
Ancora non funziona per me. Proverò ad eseguirlo su altri gruppi per vedere se riesco a trovare il problema.
