ピン留めされたトピックの数に対するバッジ / バッジSQL？

サポート
1

当コミュニティは、ピン留めされたトピックとグローバルにピン留めされたトピックを中心に構成されています。残念ながら、ユーザープロフィールページにはこれらの統計カウントが一切表示されておらず、データベース内にもピン留めやグローバルピン留めが体系的に保存されている箇所はないようです。

この状況を踏まえ、少なくとも「初回ピン留め」「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

image
image388×150 1.9 KB

追記：どうやらこれは機能するようです…

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」というリンク付きで表示することを検討してはいかがでしょうか。トピックがピン留めされることは、単なる「いいね」よりもはるかに高い評価を表すものであり、適切にカウントされるべきだと考えています。

「いいね！」 2
2

更新:

自分で解決できました。最終的なバッジクエリは以下の通りです:

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 という回避策を採用せざるを得ませんでした。また、利用可能なデータが不足しているため、このクエリを通常のピン留めとグローバルピン留めで区別することもできません。

私見ですが、エンゲージメント統計については「グローバルピン留め > 通常のピン留め > コメント > いいね > 閲覧数」という順序で評価されるべきであり、今後このように変更されるべきだと考えます。しかし、現状ではこれらは一切考慮されていません。

「いいね！」 3