Top Solution Leaderboard API code help?
Hi, all - I'm an amateur in this space, so appreciate the guidance. I have a pipe dream of surfacing our a leaderboard for internal staff who participate in different categories throughout the commun...
Forum Discussion
katiepbussey - In your query, you are using ORDER BY on is_solution(weight), where is_solution is a boolean and hence won't work with weight param. Instead, following is the correct query, where it will sort messages based on whether or not they have been accepted as a solution. Descending order displays solutions first.
SELECT author FROM messages WHERE is_solution = true ORDER BY is_solution DESC LIMIT 9
You can get more details about this query here.
In addition to this, do you want to show the leaderboard with only the internal staff? In that case, since you already would have a role associated with your internal staff, so in the above query, you can also pass the same role to get them in the leaderboard.
I hope this helps.
VarunGrazitti please help me understand, I don't get why this would make sense:
WHERE is_solution = true ORDER BY is_solution DESC LIMIT 9
if we already query messages that are solutions (and ONLY those, the result will NOT contain any non-solutions if the API works correctly, right?), why would we order them, I mean you can just leave the ORDER BY out of the query, it doesn't matter, all of the values will be true, no?
Furthermore, I would be very interested in how you would do what you suggest:
> "[...] in the above query, you can also pass the same role to get them in the leaderboard [...]"
How can we filter the messages collection by author roles? I admin, that would be incredibly useful, but I can't find anything that would support your claim that it is possible?
katiepbussey personally I think the solution is not as easy as Varun suggests, actually, there is no elegant solution I can think of if you need to aggregate that leaderboard OUTSIDE of the board/category where the solutions "live", otherwise, just use the OOB component 😃, add it to a page and it will display top-solution-authors scoped to the node you're including it in (e.g. category page, board page or community page😞
<@component id="solutions.widget.accepted-solutions-leaderboard-taplet" />
If you need that leaderboard to live outside of the scope of the above OOB component, there are various routes you could take, but each of them has flaws:
a) go over the users collection and simply use solutions_authored.count(*) => problem: no scoping to a specific node, e.g. if you need the count scoped to a category or board, this option is out of the window, at least as a "single-query-solution". It is basically what you would get if you put the OOB component on community level.
b) go your proposed route (and don't worry about the ordering, no need if you're looking for a ranking/count per author) but it will involve some (*much) more work, plus some narrowing down of criteria your component should meet: You might want to consider limiting the time-interval you're considering, e.g. are you interested in your all-time champion solution-authors or in the solution-authors of the month (for example)? The answer to the previous question determines the route forward, why: You have to also consider the amount of messages you will be dealing with, e.g. what number do you expect from the following query:
SELECT count(*) FROM messages WHERE ancestors.id = '<your.category.or.board.id>' AND is_solution = true
if you know this number, it will help you determine:
1) the value for the LIMIT clause you need to consider when querying actual message-objects (if your count is over 1000 you will have to deal with pagination, and that sucks... and if you do not specify it, you'll just get back 25 objects instead of all of them...) and
2) what kind of performance hit you could potentially cause to create your pipe-dream 😃 because you need to loop over all the objects you fetch (which takes time as well) to order them based on your criteria, and you WILL have to do additonal API queries for each item, depending on before-mentioned criteria (for example to check roles of the authors..., do staff members maybe have a special rank, then we might be able to avoid that query?)
c) go the "hacky" route by using JavaScript/AJAX to fetch and parse the leaderboards created by the scoped OOB component and re-assemble them in your staff-category 😃
before I try to provide more guidance, I need to better understand what you're looking for:
- Do you want a leaderboard for EACH category you have, e.g. the top-solution-authors WITHIN each category, or a leaderboard aggregating top-solution-authors over the entire community?
- As a consequence of the last question: Is there a single or multiple leaderboards?
- You write "I'd hope this leaderboard would show in a separate category for staff" => As I understand that you have an "internal" area within your community, that is locked down with a certain role and can only be accessed by staff, and that is where you would like to display that leaderboard or leaderboards? => yes would mean using the OOB component is not an option.
- Is your internal/staff category a top-level-category (e.g. directly under the community node)?
