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>
Ich habe damit herumgespielt und es fast zum Laufen gebracht, aber ich konnte es nicht so auf alle Kategorien anwenden, wie ich es wollte. Gibt es eine einfache Möglichkeit, das zu tun?
Hallo @Firepup650 Vielleicht versuchen Sie es mit diesem. Es hat auf meiner Instanz funktioniert.
<CATEGORY NAME> = Groß-/Kleinschreibung beachtender Kategoriename (nicht Slug)
<GROUP> = Gruppenname (z.B. Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = Mindestanzahl an Likes, die Sie festlegen möchten
<POST COUNT THRESHOLD> = Mindestanzahl an Beiträgen
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 '<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 '<GROUP NAME>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
Für mehrere Kategorien können Sie Folgendes tun:
<CATEGORY NAMES> = Groß-/Kleinschreibung beachtende Kategorienamen
<GROUP> = Gruppenname (z.B. Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = Mindestanzahl an Likes, die Sie festlegen möchten
<POST COUNT THRESHOLD> = Mindestanzahl an Beiträgen
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['<CATEGORY NAME 1>', '<CATEGORY NAME 2>', '<CATEGORY NAME 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 '<GROUP>' )
)
) >= <MINIMUM LIKE COUNT>
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>
Das sehen beides nach großartigen Abfragen aus, aber ich wollte nur fragen, ob es möglich ist, die Abfrage gegen alle Kategorien auszuführen. Als ich es versuchte, bekam ich immer wieder Fehler, dass eine Unterabfrage mehrere Zeilen zurückgab, deshalb kam ich hierher, um danach zu fragen.
Meinen Sie, dass Sie die gleiche Abfrage für alle Kategorien wünschen?
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>
Das scheint zu funktionieren, aber es scheint fehlerhaft zu sein, wenn die Gruppe, gegen die Sie es ausführen, staff ist. Ich habe sowohl Staff als auch staff als Gruppennamen versucht und die Beitrags- und Like-Anzahl vorübergehend auf 1 gesetzt, und es wird angezeigt, dass keine Abzeichen vergeben werden. Was mache ich hier falsch?
hmm ich habe Kleinschreibung staff verwendet und es hat für mich funktioniert.
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
Seltsam Bei mir funktioniert es immer noch nicht. Ich werde versuchen, es gegen ein paar andere Gruppen laufen zu lassen, um zu sehen, ob ich das Problem finden kann.
Bearbeiten: Gegen eine andere Gruppe ausgeführt, und die Abfrage schlug immer noch fehl. Ich bin mir nicht sicher, woran das liegen könnte. Hängt es zufällig von primären Gruppen ab?
Bearbeiten 2: Das würde nicht funktionieren, staff scheint nicht als primäre Gruppe festgelegt werden zu können.
Ich glaube, ich weiß warum. Ich werde daran für Sie arbeiten, nachdem ich zu Abend gegessen habe. Ich brauche sowieso SQL-Übung. Badge SQL ist restriktiver als PostgreSQL. Ich habe den Subquery-Teil geschafft.
Ich bin noch nicht ganz wach und finde, dass Abfragen zu Auszeichnungen erst nach zwei Tassen Tee vollständig bearbeitet werden können, aber ich habe kürzlich mit dem Bot über diese Art von Abfragen gesprochen und glaube, dass die Verwendung der tatsächlichen post_action_code_id und der group_id besser ist, als verschachtelte SELECT-Abfragen zu verwenden, um dasselbe zu finden.
Ich habe dies getan, um die notwendigen Schematabellen für posts, posts_actions, group_users und groups zu erhalten
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';
Dann habe ich dies verwendet, um alle Gruppen-IDs zu erhalten:
SELECT name, id FROM groups ORDER BY name
Dann habe ich alle benötigten Schematabellen einbezogen und Lola, äh GPTbot, angewiesen, die tatsächliche post_action_code-ID und den group_id-Code zu verwenden. Dann, nach einigem Hin und Her und einigen Korrekturen, kamen wir zu diesem Ergebnis. Wieder scheint es im Data Explorer zu funktionieren, aber ich bekomme im Badge Previewer immer noch nichts heraus.
G = group_id
X = minimum number of likes
Y = minimum number of 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
Ich nenne meinen Bert. Obwohl wir eine komplizierte Beziehung haben.
Ich denke, eine weitere Einschränkung dieser Art von Abfrage ist die Verwendung von MIN(p.created_at) granted_at, die mir das Datum ihres ersten gibt und nicht, sagen wir, das Datum ihres 10. Es könnte zu MAX geändert werden, aber das würde auch ein ‘nicht richtiges’ Datum ergeben, wenn Sie es gegen historische Daten ausführen, bei denen sie bereits mehr als 10 hatten.
Ich grüble noch darüber nach.
Ich hatte einige Erfolge mit ROW_NUMBER(), aber noch nichts Konkretes.
Ich habe damit aber Spaß und es hilft mir, SQL neu zu lernen und bessere Abfragen zu schreiben. Lola / GPT4bot als SQL-Assistentin ist hilfreich, aber man muss sie anleiten und sie richtig fragen. Ich versuche, Wege zu finden, ihr Zugang zu den meisten Informationen der Schematabellen zu geben, damit ich das nicht für jedes Abfrageproblem tun muss, an dem wir arbeiten. Die Angabe von Tabellenschema-Informationen liefert viel bessere Ergebnisse. Ich habe versucht, ihr einen Link zu den verfügbaren Schemata in Core zu geben, aber das hat sie nur dazu gebracht, auf Google herumzusuchen.
Ich bin daran interessiert, mit ihr zu arbeiten, wenn ich weiß, dass der Badge Query Previewer funktioniert. Ich muss SQL üben und Badge-Abfragen erstellen. Übrigens kann sie es nicht reparieren und sie macht meinen Earl Grey Tee immer noch nicht heiß genug. Obwohl die SQL-Lektion gestern Abend das beste Date war, das ich seit Jahren hatte.
Mit dieser Abfrage scheinen wir ein seltsames Problem gehabt zu haben. Sie scheint nur an Mitarbeiter vergeben worden zu sein, und ich bin mir ziemlich sicher, dass einige Nicht-Mitarbeiter diese Kriterien erfüllen würden. Ist das etwas, das ich irgendwo kaputt gemacht habe, oder ist es ein Abfrageproblem?
Nur um einen Neustart zu haben, da ich glaube, dass der Hin- und Her-Austausch mich verwirrt hat.
Ist das Ziel, eine Auszeichnung für eine bestimmte Anzahl von Beiträgen in allen Kategorien zu gewähren, die von @staff mindestens einmal mit „Gefällt mir“ markiert wurden?
Ich wollte, dass es Benutzern gewährt wird, die X Anzahl von Beiträgen mit Y Likes von Mitarbeitern in allen Kategorien haben. In meinem Fall 10 Beiträge, 5 Likes.
Nach einem verwirrenden Aussetzer mit einigen gelöschten Likes, die meine Tests durcheinanderbrachten, denke ich, dass dies eine überarbeitete Version der im OP ist, die Ihren Kriterien entspricht:
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
Es funktioniert mit der badge_posts-Ansicht, sodass nur Beiträge aus öffentlichen Kategorien gezählt werden, was Sie je nach Forum-/Kategorie-Einrichtung berücksichtigen möchten. Auch die Verwendung von CURRENT_TIMESTAMP für granted_at ist eine weitere Option, aber wahrscheinlich eine Frage des Geschmacks.
Vielleicht versuchen Sie es mit diesem. Es hat auf meiner Instanz funktioniert.
Bei mir funktioniert es immer noch nicht. Ich werde versuchen, es gegen ein paar andere Gruppen laufen zu lassen, um zu sehen, ob ich das Problem finden kann.
