# ピン留めされたトピックの数に対するバッジ / バッジSQL？
**URL:** https://meta.discourse.org/t/badges-for-number-of-pinned-topics-badge-sql/163080
**Category:** Support
**Created:** [2020 年 9 月 4 日午後 9:36 UTC](https://meta.discourse.org/t/badges-for-number-of-pinned-topics-badge-sql/163080 "2020-09-04T21:36:23Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@Terrapop](https://meta.discourse.org/u/Terrapop)
#### Post date: [2020 年 9 月 4 日午後 9:36 UTC](https://meta.discourse.org/t/badges-for-number-of-pinned-topics-badge-sql/163080/1 "2020-09-04T21:36:23Z")
当コミュニティは、ピン留めされたトピックとグローバルにピン留めされたトピックを中心に構成されています。残念ながら、ユーザープロフィールページにはこれらの統計カウントが一切表示されておらず、データベース内にもピン留めやグローバルピン留めが体系的に保存されている箇所はないようです。
この状況を踏まえ、少なくとも「初回ピン留め」「10 回ピン留め」「100 回ピン留め」、ならびに「グローバルにピン留めされたトピック」に対してバッジを付与する方法を探しています。
これらのバッジに対応する SQL クエリについて、どなたかお手伝いいただけますでしょうか？
私の着手点は以下の通りです：
```
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.unpinned IS NOT NULL
GROUP BY p.user_id
HAVING count(*) > 5
```
しかし、このクエリを実行すると以下のエラーが発生します：
ERROR: column t.unpinned does not exist
LINE 6: WHERE t.unpinned IS NOT NULL
おそらく、unpinned フィールドを使用する必要があるのでしょう。少なくとも私の知る限り、一度ピン留めされたトピックに付随する他のデータポイントはないためです。あるいは、ここで post\_actions を利用することは可能でしょうか？
あるいは、スタッフやシステムユーザーによって作成されたトピック内の最初の投稿で「pinned … ago」という文字列を含むものを検索し、カウントする方法はいかがでしょうか？action\_code =\> pinned.disabled
追記：どうやらこれは機能するようです…
```
SELECT t.user_id, current_timestamp granted_at
FROM badge_posts p
INNER JOIN topics t on t.id = p.topic_id
WHERE p.action_code ilike 'pinned.disabled'
GROUP BY t.user_id
HAVING count(*) > 0
```
ただし、これが最善のアプローチかどうかは確信が持てません。また、トピックが複数回ピン留め・ピン留め解除される可能性があるため、このクエリが各 topic.id に対して「pinned.disabled」を 1 回のみ検出しているのかどうかも不明です。
バッジ SQL のエキスパートの方のご助言をいただければ幸いです。
あるいは、今後「いいね」などと同様に、ピン留め情報をデータベースに体系的に保存し、ユーザープロフィールの統計セクションやその下に「GOT PINNED」というリンク付きで表示することを検討してはいかがでしょうか。トピックがピン留めされることは、単なる「いいね」よりもはるかに高い評価を表すものであり、適切にカウントされるべきだと考えています。
---
### Author:  [@Terrapop](https://meta.discourse.org/u/Terrapop)
#### Post date: [2020 年 9 月 6 日午前 11:05 UTC](https://meta.discourse.org/t/badges-for-number-of-pinned-topics-badge-sql/163080/2 "2020-09-06T11:05:57Z")
更新:
自分で解決できました。最終的なバッジクエリは以下の通りです:
```
WITH unpinned AS ( /* 少なくとも1つの pinned.disabled を持つ対象トピックを取得 */
SELECT t.user_id, t.id
FROM badge_posts p
INNER JOIN topics t on t.id = p.topic_id
WHERE p.action_code ilike 'pinned.disabled'
GROUP BY t.id, t.user_id
HAVING count(*) > 0
)
SELECT user_id, count(*), current_timestamp granted_at
FROM unpinned
GROUP BY user_id
HAVING count(*) > 0
```
最後の count は、過去にピン留めされたトピックの数に変更する必要があります。例えば、\>0 は少なくとも1つのピン留めトピック、\>10 は少なくとも10の過去にピン留めされたトピックを意味します。
Discourse のデータベースにはピン留めの履歴に関する他のデータポイントが存在しないため、pinned.disabled という回避策を採用せざるを得ませんでした。また、利用可能なデータが不足しているため、このクエリを通常のピン留めとグローバルピン留めで区別することもできません。
私見ですが、エンゲージメント統計については「グローバルピン留め \> 通常のピン留め \> コメント \> いいね \> 閲覧数」という順序で評価されるべきであり、今後このように変更されるべきだと考えます。しかし、現状ではこれらは一切考慮されていません。