データエクスプローラー クエリ: 返信のない投稿を検索

コミュニティ データとレポート
1

トピックのフォロー: How to find topics without a reply from someone other than the topic owner?

このクエリを作成しましたが、私の環境では問題が発生しています。

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "t"
LINE 31:    AND t.category_id = ANY ('{48,23}'::int[])

クエリコードは以下の通りです。

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)

SELECT 
p.created_at,
p.topic_id,
p.id as post_id,
p.like_count,
p.post_number,
p.reply_count
FROM posts p
LEFT JOIN post_search_data psd ON psd.post_id = p.id
RIGHT JOIN query_period qp
    ON p.created_at >= qp.period_start
        AND p.created_at <= qp.period_end
WHERE 
      reply_count = 0
      AND post_number != 0
      AND t.category_id = ANY ('{48,23}'::int[])

質問は以下の 2 点です。

  1. Categories テーブルをどのように定義すれば、このエラーが発生するのでしょうか？
  2. 期間を現在の日の翌日から開始するにはどうすればよいでしょうか？
「いいね！」 1
2

このエラーは、posts テーブルに category_id カラムが存在しないことが原因です。動作させるには、topics テーブルとの結合を行う必要があります。

次のようにしてください：
LEFT JOIN topics t ON t.id = p.topic_id

必要なデータを詳しく教えていただければ、クエリを調整できるよう努めます。
カテゴリ 48 と 23 に属するトピックではなく、すべての投稿をお探しでしょうか？また、削除された投稿やトピックは結果から除外すべきでしょうか？

CURRENT DAY() は本日の日付を返します。検索したい期間を具体的に教えてください。

「いいね！」 3
3

返信ありがとうございます！

その通りです

その通りです（トピックはクエリに含めないでください）

現在時刻から 24 時間を引いた時刻を起点とした 1 ヶ月間

「いいね！」 2
4

削除されたトピックにリンクされた投稿も除外されました。

クエリで考慮された期間: period_start: 2021-01-14 / period_end: 2021-02-14

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        DATE_TRUNC('day', CURRENT_DATE - INTERVAL '1 day') - INTERVAL ':months_ago months' AS period_start,
        (CURRENT_DATE - INTERVAL '1 day')  AS period_end
)

SELECT 
    p.created_at,
    p.topic_id,
    p.id AS post_id,
    p.like_count,
    p.post_number,
    p.reply_count
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.reply_count = 0
    AND p.post_number > 1
    AND t.category_id IN (48, 23)
    AND p.deleted_at IS NULL
    AND t.deleted_at IS NULL
    AND p.created_at >= (SELECT period_start FROM query_period)
    AND p.created_at <= (SELECT period_end FROM query_period)
「いいね！」 3
5

素晴らしいです！コードが動作しています。

ただし、テストを行ったところ、除外すべきユーザーの投稿も結果に含まれていることが分かりました。当フォーラムでは、モデレーション機能は特定のグループに所属するメンバーによって行われています。

クエリで、特定のグループに所属するユーザーの投稿を除外することは可能でしょうか？

トピックページの .json ファイルを確認したところ、可視形式で追跡されているユーザーの詳細は以下の通りです：

  • “moderator”
  • “admin”
  • “staff”
  • primary_group_name（当フォーラムでは設定されていません。モデレーターは異なるグループに所属する可能性があるためです）
「いいね！」 3