Data explorer Abfrage: Beiträge ohne Antworten finden

Gemeinschaft Daten & Berichterstattung
1

Im Anschluss an das Thema: How to find topics without a reply from someone other than the topic owner?

habe ich diese Abfrage erstellt, aber in meinem Fall liegt ein Problem vor.

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

Hier ist der Code der Abfrage:

-- [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[])

Zwei Fragen habe ich dazu:

  1. Wie definiere ich die Tabelle „Categories" und bekomme diesen Fehler?
  2. Wie kann ich den Zeitraum einen Tag nach dem heutigen Tag beginnen lassen?
1 „Gefällt mir“
2

Der Fehler tritt auf, weil in der Tabelle „posts“ keine Spalte „category_id“ vorhanden ist. Damit es funktioniert, müssen Sie einen Join mit der Tabelle „topics“ durchführen.

So zum Beispiel:
LEFT JOIN topics t ON t.id = p.topic_id

Wenn Sie die benötigten Daten genauer beschreiben, kann ich versuchen, die Abfrage anzupassen.

Suchen Sie alle Beiträge, nicht Themen, die zu den Kategorien 48 und 23 gehören? Sollen gelöschte Beiträge und Themen im Ergebnis ignoriert werden?

Die Funktion CURRENT DAY() liefert das heutige Datum. Welchen Zeitraum möchten Sie durchsuchen?

3 „Gefällt mir“
3

Vielen Dank für die Antwort!

Richtig.

Richtig (Themen sollten nicht in die Abfrage einbezogen werden).

1 Monat ab dem Zeitstempel (Aktuelle Zeit - 24 Stunden).

2 „Gefällt mir“
4

Auch Beiträge, die mit gelöschten Themen verknüpft waren, wurden ignoriert.

In der Abfrage berücksichtigter Zeitraum: 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 „Gefällt mir“
5

Super! Der Code funktioniert.

Aber beim Testen habe ich festgestellt, dass die Ergebnisse auch Beiträge von Benutzern enthalten, die ausgeschlossen werden sollten. In unserem Forum wird die Moderation von Mitgliedern bestimmter Gruppen übernommen.

Können wir in der Abfrage Beiträge von Benutzern ausschließen, die bestimmten Gruppen angehören?

Ich habe die .json-Datei der Themenseite geprüft und festgestellt, dass sie nur diese Benutzerdetails sichtbar verfolgt:

  • “moderator”
  • “admin”
  • “staff”
  • primary_group_name (ist in unserem Forum nicht gesetzt, da Moderatoren verschiedenen Gruppen angehören können)
3 „Gefällt mir“