Consulta do explorador de dados: encontrar posts sem respostas

Comunidade Dados e relatórios
1

Seguindo o tópico: How to find topics without a reply from someone other than the topic owner?

Criei esta consulta, mas no meu caso há um problema.

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

Aqui está o código da consulta:

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

Tenho 2 perguntas:

  1. Como definir a tabela Categories e obter esse erro?
  2. Como iniciar o período 1 dia após o dia atual?
2

O erro ocorre porque não há uma coluna category_id na tabela posts. Para funcionar, você precisa fazer um join com a tabela topics.

Assim:
LEFT JOIN topics t ON t.id = p.topic_id

Se você detalhar os dados que precisa, posso tentar ajustar a consulta.
Você está procurando por todos os posts, e não pelos tópicos, que pertencem às categorias 48 e 23? Posts e tópicos que foram excluídos devem ser ignorados no resultado?

O CURRENT DAY() retorna a data de hoje. Qual é o período que você deseja pesquisar?

3

Obrigado pela resposta!

Correto

Correto (tópicos não devem ser incluídos na consulta)/

1 mês a partir do timestamp (Tempo atual - 24 horas)

4

Postagens vinculadas a tópicos excluídos também foram desconsideradas.

Período considerado na consulta: 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)
5

Ótimo! O código está funcionando.

Mas ao testá-lo, descobri que os resultados também incluem posts de usuários que deveriam ser excluídos. Em nosso fórum, a função de moderação é realizada por membros de grupos específicos.

Podemos excluir na consulta os posts de usuários pertencentes a certos grupos?

Verifiquei o arquivo .json da página do tópico e descobri que ele rastreia de forma visível apenas esses detalhes do usuário:

  • “moderator”
  • “admin”
  • “staff”
  • primary_group_name (não é definido em nosso fórum, pois os moderadores podem pertencer a diferentes grupos)