Badge for posts with Likes from a specific group

[fefrei (Felix Freiberger)]

As already mentioned here:

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>

[Firepup650 (Firepup Sixfifty)]

これをいじっていたのですが、ほとんど動作するところまでいったのですが、意図したとおりにすべてのカテゴリに適用することができませんでした。簡単な方法はありますか？

[Lilly (Lillian Louis)]

Hi @Firepup650 maybe try this one. it worked on my instance.

<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>

[Firepup650 (Firepup Sixfifty)]

こんにちは、@Lillyさん！

どちらも素晴らしいクエリのように見えますが、可能であればすべてのカテゴリに対してクエリを実行したいと考えています。試してみたところ、「サブクエリが複数の行を返した」というエラーが繰り返し発生したため、ここで質問させていただきました。

[Lilly (Lillian Louis)]

すべてのカテゴリで同じクエリをご希望ということですか？

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>

[Firepup650 (Firepup Sixfifty)]

それが機能するように思えますが、実行しているグループが staff の場合に失敗するように思えます。グループ名として Staff と staff の両方を試しましたが、投稿数といいね数を一時的に 1 に設定しましたが、バッジは付与されないと表示されます。ここで何が間違っていますか？

[Lilly (Lillian Louis)]

大文字小文字を区別しない 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

[Firepup650 (Firepup Sixfifty)]

変ですね まだ私には機能しません。問題を特定するために、他のいくつかのグループに対して実行してみます。

編集: 別のグループに対して実行しましたが、クエリは失敗しました。何が問題なのかわかりません。偶然、プライマリグループに依存しますか？

編集 2: それは機能しません、staff はプライマリグループとして設定できないようです。

[Lilly (Lillian Louis)]

夕食を食べてから、これに取り組みます。いずれにせよ、SQLの練習が必要です。バッジSQLはPostgreSQLよりも制限が厳しいです。サブクエリの部分はクリアしました。

[JammyDodger]

まだ完全に目が覚めていないので、バッジのクエリは本格的に取り組む前にしっかり紅茶を2杯飲む必要がありますが、最近ボットとこのようなクエリについて話していて、ネストされたSELECTクエリを使用して同じものを見つけるよりも、実際のpost_action_code_idとgroup_idコードを使用する方が良いと思います。

[Lilly (Lillian Louis)]

posts、posts_actions、group_users、groups の必要なスキーマテーブルを取得するためにこれを行いました。

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<TABLE NAME>';

次に、これを使用してすべてのグループ ID を取得しました。

SELECT name, id FROM groups ORDER BY name

その後、必要なすべてのスキーマテーブルを含め、Lola、いや GPTbot に実際の post_action_code ID と group_id コードを使用するように指示しました。その後、何度かやり取りと修正を経て、これに至りました。これも Data Explorer では機能するようですが、Badge Previewer では何も取得できません。

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

はい、GP4bot を Lola と名付けました

[JammyDodger]

私は自分のをバートと呼んでいます。 ただ、複雑な関係ではありますが。\n\nその種のクエリのもう一つの制限は、MIN(p.created_at) granted_at を使用すると、最初のものの日付が得られ、例えば10番目のものの日付が得られないことだと思います。MAX に変更することもできますが、10以上持っていた過去のデータに対して実行した場合、間違った日付になってしまう可能性があります。\n\nまだそのことについて考えています。\n\nROW_NUMBER() を使用してある程度の成功を収めましたが、まだ具体的なものはありません。

[Lilly (Lillian Louis)]

ええ、同意します。まだ何かがしっくりきません。寝ますね。

[Lilly (Lillian Louis)]

しかし、これは楽しいですし、SQLとより良いクエリの書き方を再学習するのに役立っています。Lola / GPT4botをSQLアシスタントとして使うのは役立ちますが、彼女を導き、正しい方法で質問する必要があります。クエリの問題ごとに私がやる必要がないように、スキーマテーブル情報のほとんどに彼女がアクセスできるようにする方法を模索していました。テーブルスキーマ情報を提供すると、はるかに良い結果が得られます。コアの利用可能なスキーマへのリンクを提供しようとしましたが、彼女はGoogleで時間を無駄にするだけでした。

バッジクエリプレビューアが機能しているとわかったら、彼女と一緒に作業することに興味があります。SQLとバッジクエリの練習が必要です。ちなみに、彼女はそれを修正できず、私のアーリーアールグレイティーをまだ十分に熱くしてくれません。昨夜のSQLレッスンは、ここ数年で最高のデートでしたが。

[Firepup650 (Firepup Sixfifty)]

そのクエリを使用したところ、奇妙な問題が発生しました。スタッフのみに付与されたようで、スタッフではない人もその基準を満たすとほぼ確信しています。これは私がどこかで壊したものでしょうか、それともクエリの問題でしょうか？

[Lilly (Lillian Louis)]

ええ、何か問題があることはわかっています。プレビュー修正でインスタンスが更新され次第、すぐにこれに取り組みます。

[JammyDodger]

混乱を招いたようなので、一度リセットさせてください。

これは、すべてのカテゴリにわたる一定数の投稿に対して、@staff によって少なくとも一度「いいね」されたバッジを付与することを目的としていますか？

[Firepup650 (Firepup Sixfifty)]

スタッフによって、すべてのカテゴリにわたって X 個の投稿と Y 個の「いいね！」を獲得したユーザーに付与されることを意図していました。私の場合は、投稿10件、いいね！5件です。

[JammyDodger]

一部の「いいね！」が削除されてテストが狂ってしまったため、混乱がありましたが、OPにあるものに条件を満たすように修正したバージョンだと思います。

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 ビューから機能するため、公開カテゴリからの投稿のみをカウントします。これは、フォーラム/カテゴリの設定によっては考慮する必要があるかもしれません。また、granted_at に CURRENT_TIMESTAMP を使用することも別の選択肢ですが、おそらく好みの問題です。