Want to create a date range filter for two tables

Get Help
1

Hi,
I was trying to create a date range filter that would query the two tables for dates but I am unable to do it. Please find the below query. I was trying to create two separate filters for each. but that's something that I don't want I want to query the final output of both the tables with a date range value.
WITH
post_filter as (
select p.id,
p.description,
p.created_at,
p.comment_count,
p.reaction_count,
p.status,
p.user_id
FROM posts p
where {{created_at_posts}}
),

comments_filter as (
select
c.id,
c.comment_text,
c.commentable_id,
c.created_at,
c.reaction_count,
c.status,
c.user_id
FROM comments c
where {{created_at_comments}}
),

posts AS
(
SELECT u.external_reference_id AS post_farmer_id,
u.name AS post_farmer_name,
p.id AS post_id,
p.description AS post_description,
m.url AS post_attachment_url,
p.created_at AS post_created_at,
p.comment_count AS post_comment_count,
p.reaction_count AS post_like_count,
p.status AS post_status,
p.user_id AS post_user_id,
u.metadata->>'stateName' AS post_state,
t.topic_value AS topic
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN media m ON m.post_id = p.id
LEFT JOIN posts_topics pt ON pt.post_id = p.id
LEFT JOIN topics t ON t.id = pt.topic_id
INNER JOIN post_filter pf ON pf.id=p.id
),

comments AS
(
SELECT u.external_reference_id AS comment_farmer_id,
u.name AS comment_farmer_name,
c.id AS comment_id,
c.comment_text AS comment_content,
c.commentable_id AS commentable_id,
c.created_at AS commented_At ,
c.reaction_count AS comment_like_count,
c.status AS comment_status,
c.user_id AS comment_user_id,
u.metadata->>'stateName' AS comment_state
FROM comments c
INNER JOIN users u ON c.user_id = u.id
INNER JOIN comments_filter on c.id=c.id
)

SELECT
p.,
c.
FROM posts p
LEFT JOIN comments c ON p.post_id = c.commentable_id

2

Hi! So you have a post_created_at and a comment_created_at, and you want a single date filter right?

You can do something like this in the last part of your query:

SELECT
p.,
c.
GREATEST(p.created_at, c.created_at) AS latest_post_or_comment_at
FROM posts p
LEFT JOIN comments c ON p.post_id = c.commentable_id
WHERE {{ post_or_comment_date }}

This will let you use the date filter on the most recent post or comment date for a given row. Note that GREATEST function is only available for some SQL dialects :slight_smile:

Let me know if this helps!

3

Hi @nllho ,

Thank you for your response. I am using PostgreSQL dialect.
I modified the query you mentioned but getting the below error. The new modified query:-

WITH posts AS
(
SELECT u.external_reference_id AS post_farmer_id,
u.name AS post_farmer_name,
p.id AS post_id,
p.description AS post_description,
m.url AS post_attachment_url,
p.created_at AS post_created_at,
p.comment_count AS post_comment_count,
p.reaction_count AS post_like_count,
p.status AS post_status,
p.user_id AS post_user_id,
u.metadata->>'stateName' AS post_state,
t.topic_value AS topic
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN media m ON m.post_id = p.id
LEFT JOIN posts_topics pt ON pt.post_id = p.id
LEFT JOIN topics t ON t.id = pt.topic_id
),

comments AS
(
SELECT u.external_reference_id AS comment_farmer_id,
u.name AS comment_farmer_name,
c.id AS comment_id,
c.comment_text AS comment_content,
c.commentable_id AS commentable_id,
c.created_at AS commented_At ,
c.reaction_count AS comment_like_count,
c.status AS comment_status,
c.user_id AS comment_user_id,
u.metadata->>'stateName' AS comment_state
FROM comments c
INNER JOIN users u ON c.user_id = u.id
)

SELECT
p.,
c.,
GREATEST(p.post_created_at, c.commented_At) AS latest_post_or_comment_at

FROM posts p
LEFT JOIN comments c ON p.post_id = c.commentable_id
WHERE {{ post_or_comment_date }}

ERROR: missing FROM-clause entry for table "posts" Position: 1169

4

I tried this out and it is fixed now. Below is the query for the same. Thanks @nllho
WITH posts AS
(SELECT u.external_reference_id AS post_farmer_id,
u.name AS post_farmer_name,
p.id AS post_id,
p.description AS post_description,
m.url AS post_attachment_url,
p.created_at AS post_created_at,
p.comment_count AS post_comment_count,
p.reaction_count AS post_like_count,
p.status AS post_status,
p.user_id AS post_user_id,
u.metadata->>'stateName' AS post_state,
t.topic_value AS topic
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN media m ON m.post_id = p.id
LEFT JOIN posts_topics pt ON pt.post_id = p.id
LEFT JOIN topics t ON t.id = pt.topic_id
WHERE p.created_at BETWEEN {{start_date}} AND {{end_date}}),

comments AS
(SELECT u.external_reference_id AS comment_farmer_id,
u.name AS comment_farmer_name,
c.id AS comment_id,
c.comment_text AS comment_content,
c.commentable_id AS commentable_id,
c.created_at AS commented_At ,
c.reaction_count AS comment_like_count,
c.status AS comment_status,
c.user_id AS comment_user_id,
u.metadata->>'stateName' AS comment_state
FROM comments c
INNER JOIN users u ON c.user_id = u.id
WHERE c.created_at BETWEEN {{start_date}} AND {{end_date}})

SELECT *
FROM posts p
LEFT JOIN comments c ON p.post_id = c.commentable_id
ORDER BY p.post_created_at

1 Like