Data explorer query: find posts without replies

howto tips & tricks
#1

Following the topic: How to find posts (not first posts and not topics!) without a reply?

I created this query, but in my case there is a problem.

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

Here is the query’ code:

-- [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 questions I have:

  1. How to define the Categories table and get this error?
  2. How to start the period 1 day after the current day?
1 Like
#2

The error is because there is no category_id column in the posts table. To work, you need to do a join with the topics table.

Like this:
LEFT JOIN topics t ON t.id = p.topic_id

If you detail the data you need, I can try to adjust the query.
What are you looking for are all posts, not topics, that belong to categories 48 and 23? Should posts and topics that have been deleted be disregarded in the result?

The CURRENT DAY() brings today’s date. What is the period you want to search for?

3 Likes
#3

Thank you for a reply!

Correct

Correct (topics shouldn’t be included in the query)/

1 month starting from the (Current time - 24 hours) timestamp

2 Likes
#4

Posts linked to deleted topics were also disregarded.

Period considered in the 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 Likes
#5

Great! The code is working.

But when I’ve tested it, I found that results also contain posts from users that should be excluded. On our forum the moderation function is done by members of specific groups.

Can we exclude in the query posts from users belonging to some groups?

I’ve checked .json file of the topic page and found that it tracks in a visible form only these user details:

  • “moderator”
  • “admin”
  • “staff”
  • primary_group_name (is not set on our forum, because moderators can belong to different groups)
2 Likes