Help with sql - autoclose/repeat results/week filter

Community Building Data & reporting

AI-generated summary

Matt0x01 seeks help with an SQL query to filter topics by category, vote count, and creation date, while addressing issues of duplicate results and auto-close timing. pfaffman identifies the root cause of duplicate topics as a missing join condition between topics and discourse_voting_topic_vote_count, suggesting t.id = dvc.topic_id to fix it. Matt0x01 confirms the fix resolves the duplication. pfaffman then explains that the week filter issue may stem from time zone or timestamp precision, and suggests adding topic_timers to the query to retrieve auto-close dates, requiring a join on tt.topic_id = t.id. Matt0x01 thanks pfaffman for the assistance.

1

Hey so I’ve created a query(at the bottom) that displays topic, category, the vote count of a topic, if its closed, when it was created and the user who has created. If more info is needed please feel free to let me know.

I have a few issues and questions I’d like some help or guidance with since I’m new to sql.

  1. When running this query it displays the same topic hundreds of times over. How can I fix this

  2. Is there any way to get and show when a topic will auto close?

  3. I want only topics within a week to display. But the date_trunc i added shows some items a little over a week ago?

SELECT
    t.id as topic_id,
    t.category_id,
    dvc.votes_count,
    t.closed,
    t.created_at,
    t.user_id
FROM topics t, discourse_voting_topic_vote_count dvc
WHERE t.closed = true
    AND t.category_id = 20
    AND dvc.votes_count >= 1
    AND t.created_at <= date_trunc('week', current_date)::date
    AND t.created_at >= date_trunc('week', current_date)::date - 7
2

Try adding this after the WHERE line.

 AND t.id = dvc.topic_id

I didn’t test it, but I think that’s your big problem (fixes the “same topic” problem)

3

Awesome! Thanks Jay it works :smiley:

Would you know how to go about the other issues?

4

My guess on (3) is that it’s a time zone issue, and/or that it’s based on the second that you run the script and you want the previous or next midnight. (Not quite sure how to fix either of those offhand).

Not sure if this’ll help but you can try

To get when they’re going to close, you’d need to add something to the SELECT part (which you can infer from the available fields) and change FROM like

   FROM topics t, discourse_voting_topic_vote_count dvc, topic_timers tt

and add this after WHERE

    AND tt.topic_id=t.id

(just like the one you added before).

5

Thanks again Jay you’re a lifesaver (:

6

Wow! That worked? That’s awesome. Glad it helped.