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:
- How to define the Categories table and get this error?
- How to start the period 1 day after the current day?