Query esplora dati: trova post senza risposte

Comunità Dati e reportistica
1

Seguendo l’argomento: How to find topics without a reply from someone other than the topic owner?

Ho creato questa query, ma nel mio caso c’è un problema.

PG::UndefinedTable: ERRORE: voce FROM-clause mancante per la tabella "t"
LINE 31:    AND t.category_id = ANY ('{48,23}'::int[])

Ecco il codice della query:

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

Ho due domande:

  1. Come si definisce la tabella Categories e come si ottiene questo errore?
  2. Come si imposta l’inizio del periodo un giorno dopo il giorno corrente?
1 Mi Piace
2

L’errore si verifica perché nella tabella posts non c’è la colonna category_id. Per far funzionare la query, è necessario effettuare un join con la tabella topics.

In questo modo:
LEFT JOIN topics t ON t.id = p.topic_id

Se fornisci maggiori dettagli sui dati di cui hai bisogno, posso provare ad adattare la query.
Stai cercando tutti i post, e non i topics, che appartengono alle categorie 48 e 23? I post e i topics eliminati devono essere esclusi dal risultato?

La funzione CURRENT DAY() restituisce la data di oggi. Qual è il periodo che desideri cercare?

3 Mi Piace
3

Grazie per la risposta!

Corretto

Corretto (i topic non dovrebbero essere inclusi nella query)/

1 mese a partire dal timestamp (Tempo corrente - 24 ore)

2 Mi Piace
4

I post collegati ad argomenti cancellati sono stati anch’essi ignorati.

Periodo considerato nella query: 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 Mi Piace
5

Ottimo! Il codice funziona.

Tuttavia, durante i test, ho notato che i risultati includono anche post di utenti che dovrebbero essere esclusi. Nel nostro forum, la moderazione è affidata a membri di gruppi specifici.

È possibile escludere nella query i post di utenti appartenenti a determinati gruppi?

Ho controllato il file .json della pagina dell’argomento e ho scoperto che traccia in forma visibile solo questi dettagli utente:

  • “moderator”
  • “admin”
  • “staff”
  • primary_group_name (non è impostato nel nostro forum, poiché i moderatori possono appartenere a gruppi diversi)
3 Mi Piace