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. When running this query it displays the same topic hundreds of times over. How can I fix this Is there any way to get and show when a topic will auto close? 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 - 7

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)

Awesome! Thanks Jay it works :smiley: Would you know how to go about the other issues?

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).

Thanks again Jay you’re a lifesaver (:

עזרה עם SQL - סגירה אוטומטית/תוצאות חוזרות/סינון שבועי

קהילה נתונים ודיווח

pfaffman (Jay Pfaffman) 25 ביוני,‏ 2022,‏ 9:36am 6

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

נושא		תגובות	צפיות
Autoclose topic if set to 1 minute doesn't work until 2 minutes Bug	3	743	6 באפריל,‏ 2022
Last 7 days of topics Product Support	8	893	14 באפריל,‏ 2018
Closed topics not returning votes to users Bug topic-voting	7	2661	12 באוגוסט,‏ 2018
Mass close topics? Product Support	6	2411	18 באוקטובר,‏ 2019
Votes not being returned on topic close Bug topic-voting	7	957	12 בדצמבר,‏ 2023

עזרה עם SQL - סגירה אוטומטית/תוצאות חוזרות/סינון שבועי

נושאים קשורים