The discussion revolves around the Data Explorer plugin and incorporating vote counts from the Topic Voting plugin. tyf shared a query to retrieve topics from the Feedback category but needed assistance adding vote counts. JammyDodger provided the necessary tables (discourse_voting_topic_votes and discourse_voting_votes) and an updated query to include vote counts using discourse_voting_topic_vote_count. The updated query joins the topics table with discourse_voting_topic_vote_count on the topic_id. tyf thanked JammyDodger for the solution.
I’m using the Data Explorer plugin and attempting to get topics within our Feedback category - I can achieve this fine with the following query:
SELECT
t.id AS topic_id,
t.title AS topic_title,
t.excerpt,
t.like_count,
('https://our_community.com/t/' || t.slug || '/' || t.id) AS url
FROM
topics t
WHERE
t.category_id = 95
AND
t.created_at > NOW() - INTERVAL '2 years'
AND deleted_at IS NULL
What I am unsure of is how to also include the vote count associated Topic Voting plugin. Does anyone know how I might include that for each post in the query above?
The Topic Voting tables are discourse_voting_topic_votes and discourse_voting_votes. You can include the number of votes for each topic with something like this:
SELECT
t.id AS topic_id,
t.title AS topic_title,
dvtv.votes_count,
t.excerpt,
t.like_count,
('https://our_community.com/t/' || t.slug || '/' || t.id) AS url
FROM
topics t
JOIN
discourse_voting_topic_vote_count dvtv ON t.id = dvtv.topic_id
WHERE
t.category_id = 95
AND
t.created_at > NOW() - INTERVAL '2 years'
AND deleted_at IS NULL
There are also a couple more queries you may find useful in the #data-reporting category - Topics tagged topic-voting
