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>
<CATEGORY NAME> = اسم الفئة حساس لحالة الأحرف (وليس الاسم المختصر)
<GROUP> = اسم المجموعة (مثل: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = الحد الأدنى لعدد الإعجابات التي تريد تعيينها
<POST COUNT THRESHOLD> = الحد الأدنى لعدد المشاركات
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>
للفئات المتعددة يمكنك القيام بذلك:
<CATEGORY NAMES> = أسماء الفئات الحساسة لحالة الأحرف
<GROUP> = اسم المجموعة (مثل: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = الحد الأدنى لعدد الإعجابات التي تريد تعيينها
<POST COUNT THRESHOLD> = الحد الأدنى لعدد المشاركات
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>
أهلاً @Lilly!
يبدو أن كلا الاستعلامين رائعان، لكنني أردت فقط تشغيل الاستعلام على جميع الفئات إن أمكن. عندما حاولت، كنت أواجه أخطاءً حول استعلام فرعي يُرجع صفوفًا متعددة، لذا جئت للسؤال هنا عنه.
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>
يبدو أن هذا سيعمل، ولكنه يفشل عندما تكون المجموعة التي تقوم بتشغيلها ضدها هي staff. لقد جربت كل من Staff و staff كاسم للمجموعة، وقمت مؤقتًا بتعيين عدد المنشورات والإعجابات إلى 1، ويقول إنه لن يتم منح أي شارات. ما الخطأ الذي أرتكبه هنا؟
حسناً، لقد استخدمت أحرفاً صغيرة في كلمة staff وعملت معي.
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
أعتقد أنني أعرف السبب. سأعمل على هذا من أجلك بعد تناول العشاء. أحتاج إلى تدريب على SQL على أي حال. شارة SQL أكثر تقييدًا من postgres. لقد تجاوزت جزء الاستعلام الفرعي.
لم أستيقظ تمامًا بعد، وأجد أن استعلامات الشارات تحتاج إلى كوبين من الشاي قبل أن أتمكن من معالجتها بالكامل، ولكني كنت أتحدث إلى الروبوت مؤخرًا حول هذه الأنواع من الاستعلامات، وأعتقد أن استخدام post_action_code_id الفعلي ورمز group_id أفضل من استخدام استعلامات SELECT المتداخلة للعثور على نفس الشيء.
لقد فعلت هذا للحصول على جداول المخطط الضرورية لـ posts و posts_actions و group_users و groups.
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';
ثم استخدمت هذا للحصول على جميع معرفات المجموعات:
SELECT name, id FROM groups ORDER BY name
لذلك، قمت بتضمين جميع جداول المخطط المطلوبة وأصدرت تعليمات لـ Lola، أو GPTbot لاستخدام معرف post_action_code الفعلي ومعرف group_id. ثم بعد بعض الجدال ذهابًا وإيابًا وإجراء بعض التصحيحات. توصلنا إلى هذا. مرة أخرى، يبدو أنه يعمل في مستكشف البيانات، لكنني ما زلت لا أستطيع استخراج أي شيء منه في معاينة الشارات.
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
أنا أسميه بيرت. على الرغم من أن لدينا علاقة معقدة.
أعتقد أن هناك قيدًا آخر لهذا النوع من الاستعلام وهو استخدام MIN(p.created_at) granted_at الذي يعطيني تاريخ أول واحد لهم وليس، على سبيل المثال، تاريخ العاشر لهم. يمكن تغييره إلى MAX، ولكن هذا سيعطي أيضًا تاريخًا “غير صحيح” إذا قمت بتشغيله على بيانات تاريخية حيث كان لديهم بالفعل أكثر من 10.
ما زلت أفكر في ذلك.
لقد حققت بعض النجاح باستخدام ROW_NUMBER()، ولكن لم يكن هناك شيء ملموس حتى الآن.
أنا أستمتع بهذا على الرغم من أنه يساعدني على إعادة تعلم لغة SQL وكيفية كتابة استعلامات أفضل. وجود لولا / GPT4bot كمساعد لـ SQL مفيد، ولكن عليك توجيهها وطرح الأسئلة بالطريقة الصحيحة. حاولت إيجاد طرق لمنحها الوصول إلى معظم معلومات جداول المخطط حتى لا أضطر إلى القيام بذلك لكل مشكلة استعلام نعمل عليها. إن تقديم معلومات مخطط الجدول يعطي نتائج أفضل بكثير. حاولت إعطائها رابطًا للمخططات المتاحة في core ولكن هذا جعلها تبحث في جوجل.
أنا مهتم بالعمل معها عندما أعرف أن معاينة استعلام الشارة تعمل. أحتاج إلى ممارسة لغة SQL وإجراء استعلامات الشارات. بالمناسبة، لا يمكنها إصلاحها ولا تزال تجعل شاي إيرل جراي الخاص بي ليس ساخنًا بما فيه الكفاية. على الرغم من أن درس SQL الليلة الماضية كان أفضل موعد لي منذ سنوات.
باستخدام هذا الاستعلام، يبدو أننا واجهنا مشكلة غريبة. يبدو أنه تم منحه للموظفين فقط، وأنا شبه متأكد من أن بعض غير الموظفين قد يستوفون هذا المعيار. هل هذا شيء كسرته في مكان ما، أم أنها مشكلة في الاستعلام؟
بعد وميض مربك مع بعض الإعجابات المحذوفة التي أربكت اختباراتي، أعتقد أن هذه نسخة منقحة من النسخة الموجودة في المنشور الأصلي والتي تناسب معاييرك:
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
يعمل هذا على عرض badge_posts لذا فهو يحسب فقط المشاركات من الفئات العامة، والتي قد ترغب في أخذها في الاعتبار اعتمادًا على إعدادات المنتدى/الفئة الخاصة بك. أيضًا، استخدام CURRENT_TIMESTAMP لـ granted_at هو خيار آخر، ولكنه ربما يعود إلى مسألة ذوق.
ربما جرب هذا. لقد نجح معي.
غريب، لا يزال لا يعمل لدي. سأحاول تشغيله على بعض المجموعات الأخرى لمعرفة ما إذا كان بإمكاني تحديد المشكلة.
