Badge SQLについて少し助けが必要です

サポート
1

皆さん、こんにちは。

特定のカテゴリで作成された新しいトピックの数に応じてバッジを発行するSQLクエリがあります。このトピックは、トピックの最初の投稿に特定のフレーズ（特定のURL）も含まれている必要があります。

意図したとおりには動作していませんが、機能はしています。

SELECT badge_posts.user_id, min(badge_posts.created_at) granted_at, MIN(badge_posts.id) AS post_id
FROM badge_posts
JOIN topics ON topics.id = badge_posts.topic_id
WHERE category_id = 17
AND badge_posts.post_number = 1
AND badge_posts.raw LIKE '%dronescene.co.uk%'
GROUP BY badge_posts.user_id
HAVING COUNT(*) > 49
ORDER BY post_id DESC

ブロンズ、シルバー、ゴールド、プラチナの4つのバッジがあり、ブロンズにはHAVING count(*) > 1、シルバーにはHAVING count(*) > 10などを使用しています。バッジはすべて自動的に発行され、問題はありません。

私が抱えている問題は、すべてのバッジが、そのカテゴリで最初に作成されたトピックIDに対して発行されていることです。WHERE句に一致する最後のトピックIDではなく。

たとえば、3つのバッジを持つメンバーがいますが、3つのバッジすべてが、WHERE句に一致する最新のトピックIDではなく、最初に作成されたトピックIDに対して発行されています。

ORDER BYのどこが間違っているか教えていただけますか？ :thinking:

「いいね！」 1
2

SQLはまだあまり詳しくないのですが、MIN関数でよいのでしょうか？それは最初ではなく最後を返すのではないでしょうか？

「いいね！」 2
3

トピックを SORT DESC で並べ替えると、最後のものが取得できるのではないでしょうか。

「いいね！」 2
4

もし MIN を MAX と入れ替えると、最も最近のトピック ID を取得できます（@JammyDodger さん、ありがとうございます）。しかし、プレビューの変更点を見てみると、このロジック自体が根本的に間違っているようです :grimacing:

MAX を使用すると、このグループの他のすべてのバッジが最も最近のトピック ID に対して授与されてしまいます。

問題は逆になっているだけです。

しまった :man_facepalming:

私のロジックは単純に次のようでした。

問題は、誰かが 1 月にブロンズ、3 月にシルバー、6 月にゴールドを獲得した場合、3 つすべてのバッジが 1 月の最初のトピック ID に対して授与されてしまうことです :thinking:

何かアイデアはありますか？ :man_shrugging:

「いいね！」 2
5

それは、すでに4つすべてに該当するユーザーに対して実行しているからだと思います。もし「新規」で導入された場合、ユーザーがしきい値を超えた時点で、最新のトピックの日付が自動的に付与されると思います。

「レガシー」実行の場合、LIMITを追加する必要があるかもしれません。

編集: 最初の（例として）25件の適格レコードをユーザーごとに1つのクエリで返すことは、私の現在のスキルでは難しいです。:slightly_smiling_face:

しかし、複数のバッジに該当するユーザーが何人いるかによっては、データエクスプローラーのクエリ情報を使用して手動でレガシーバッジを付与し、その後、上記の自動バッジクエリを実行することで、それらが付与された後に適切な投稿に対して正しいバッジが付与されるはずです。

以下のようなものです。

-- [params]
-- int :user_id = 1
-- int :limit = 50

SELECT bp.user_id, bp.created_at, bp.id as post_id
FROM badge_posts bp
JOIN topics t ON t.id = bp.topic_id
WHERE bp.user_id = :user_id
AND t.category_id = 17
AND bp.post_number = 1
AND bp.raw LIKE '%dronescene.co.uk%'
ORDER BY bp.created_at ASC
LIMIT :limit

タイプミスがありましたら申し訳ありません。:slightly_smiling_face:

（ブロンズはMINバージョンで、プラチナはMAXバージョンで可能なので、10と25のものだけです）

「いいね！」 2