既存のバッジクエリを確認し、badge_posts テーブルを対象とするクエリや、保護されたカテゴリからの投稿を除外する他の方法を使用しているクエリがどれか把握しました。
以下のバッジクエリは特定の投稿を対象としており、プライベートカテゴリでの活動に対しては付与されません。
- Editor
- First Flag
- First Like
- First Link
- First Quote
- First Share
- First Emoji
- First Mention
- First Onebox
- First Reply By Email
- Reader
- Wiki Editor
- Great Share
- Good Share
- Helpdesk
- Nice Share
- Welcome
- Famous Link
- Great Reply
- Great Topic
- Good Reply
- Good Topic
- Hot Link
- Nice Reply
- Nice Topic
- Popular Link
一方、以下のバッジは特定の投稿をクエリせず、プライベートカテゴリでの活動に対しても付与されます。
- Licensed
- Autobiographer
- Certified
- New User of the Month
- Read Guidelines
- Admired
- Champion
- Crazy in Love
- Devotee
- Empathetic
- Aficionado
- Anniversary
- Campaigner
- Gives Back
- Higher Love
- Respected
- Appreciated
- Enthusiast
- Out of Love
- Promoter
- Thank You
- Leader
- Regular
- Basic
- Member
- Staff
- Profile Picture
これに似た機能は、すでに「Appreciated（20 投稿で 1 ライク）」と「Respected（100 投稿で 2 ライク）」のバッジでカバーされています。これらのクエリのバリエーションを追加することも可能です。例えば、「20 投稿で 10 ライク」などです。また、スーパー「いいね」されたトピックに対して付与されるバッジも良いアイデアでしょう。これは「Great Topics」バッジに相当するものになります。例えば、ユーザーが 10 件のトピックを作成し、それぞれが 10 ライクを獲得した場合に付与されるように設定できます。
特定の投稿やトピックに対する活動に対して、その投稿へのリンクを持たないバッジを追加することは意味があるでしょうか。例えば、以下の SQL を使用して「First Like」の代替バッジを作成することも考えられます。
SELECT pa1.user_id, pa1.created_at granted_at
FROM (
SELECT pa.user_id, min(pa.id) id
FROM post_actions pa
JOIN posts p on p.id = pa.post_id
WHERE post_action_type_id = 2
GROUP BY pa.user_id
) x
JOIN post_actions pa1 on pa1.id = x.id
このクエリを機能させるには、「投稿へのユーザーアクション時」ではなく「毎日更新」トリガーを使用する必要があります。バッジページでは、バッジが付与されたユーザーとその付与時刻が表示されますが、バッジの対象となった投稿へのリンクは表示されません。
このアプローチは、主に保護されたカテゴリを持つサイトにとって意味があるでしょうか？もしそうであれば、現在 badge_posts テーブルを対象としているクエリの一部を複製するために活用できるかもしれません。