The discussion revolves around displaying the count of solved items by users on the /users page in Discourse. awlogan initiated the conversation, suggesting it would be valuable for tracking user contributions. sam and itsbhanusharma pointed out that the feature exists but only shows the total number of solved items, not on a per-user basis.
awlogan clarified that they want to see the solved count on a per-user basis on the /users summary table. itsbhanusharma and sam acknowledged it’s a custom requirement and might be complex to implement.
sam suggested creating a custom report using SQL skills and the Data Explorer plugin as a possible solution. awlogan and sam discussed the possibility of creating a query to retrieve the data.
awlogan shared their attempt at creating a query, but faced difficulties in displaying users with 0 solved solutions. awlogan eventually shared a working query that filters by group ID and includes the solved count.
Jason_Schulke requested a modification to include date reporting, and awlogan shared an example of using date ranges in a query.
Later, JammyDodger pointed out that the User Directory can be customized to include the Solutions count, and dandv confirmed this solution worked. However, dandv also asked about the update frequency of the list, and JammyDodger replied that it’s updated hourly for the ‘Today’ timeframe and daily for other timeframes.
As I get ready to open up my community to our partners and customers, we’re looking at using the user summary table (example.com/users) to help monitor what our SE community is doing.
One of the things I noticed is that while it counts likes, posts, visits, etc., it doesn’t show a count of how many items the user posted that were then marked solved. This could be very valuable for our managers to see who’s contributing and also solving issues.
I couldn’t find anything in the admin console to enable this, so it may be a feature request. I’m also on a hosted solution if that makes any difference, but I didn’t see anything in the plugin settings.
That’s why I’m asking, Discourse does a really good job of summarizing data, but this one is missing and would be really useful. This is the summary I’m talking about.
Understand it’s a feature request which is what I figured. It would certainly make it more useful for tracking what users are up to if I want to make participation in the community something I can track and report on for leadership.
Would there be a way to run it as a report instead with basically the same info? This is probably at best something I need to provide quarterly in my case, others may have different requirements.
Following up, I can get the data when there is a solution selected using the following:
SELECT value
FROM
post_custom_fields
WHERE
name = 'is_accepted_answer'
What I’m getting back is either ‘true’ which I expect from looking through the plugin code. There is one NULL, I am going to assume that this is from someone who checked a solution box then unchecked it. Unfortunately I can’t seem to get it to show other users who have 0 solved solutions when I try to build out the dashboard like you see at Discourse Meta.
It’s possible it’s just my rusty SQL skills and maybe I’m missing something obvious in how to display the rest of my user base with 0 matches. I tried matching on NULL (1 match) and an empty string but that didn’t provide any additional output.
I can certainly run it today as there are only 7 of us with solved solutions as most of my users interact via email, but as we get ready to open this up to our user and partner base I’m guessing that will go up. If anyone has any pointers I’d appreciate it.
Here is the solution I have. I am filtering by group ID as that’s my employee group that I have everyone added to when they sign up:
SELECT u.username,
u.name,
us.likes_given,
us.likes_received,
us.topic_reply_count,
us.post_count,
us.posts_read_count,
us.topic_count,
COUNT(pc.name) AS solved
FROM
users u
JOIN
posts p ON p.user_id = u.id
LEFT JOIN
post_custom_fields pc ON pc.post_id = p.id
JOIN
user_stats us ON us.user_id = u.id
WHERE u.primary_group_id = [GROUP ID]
AND pc.name = 'is_accepted_answer'
GROUP BY u.id,
us.likes_given,
us.likes_received,
us.topic_reply_count,
us.post_count,
us.posts_read_count,
us.topic_count
