Forum Discussion

darrenSP's avatar
darrenSP
Mentor
8 years ago

Get last accepted solution

Is there a way (preferably using V2 API) to get the last accepted solution within the community?

 

I can get all accepted solutions using:

 

SELECT solution_data,is_solution FROM messages WHERE is_solution = true

But ORDER BY is not allowed on any column that is returned so I cannot use the time column.

 

Any ideas?

  • VikasB's avatar
    VikasB
    8 years ago

    darrenSP  You can get the solution accepted time as nested in solution_data but you can not use nested parameter in order by.  
    Here you can use a workaround.   Use below approach

    <#assign accepted_solution = rest("2.0","/search?q=" + "SELECT solution_data,is_solution FROM messages WHERE is_solution = true"?url).data.items[0] />

    <#assign time = accepted_solution.solution_data.time?long />
    <#assign latest_soution_id = accepted_solution.solution_data.message_id />

    <#assign solutions_count = rest("2.0","/search?q=" + "SELECT count(*) FROM messages WHERE is_solution = true"?url).data.count />
    <#assign solutions = rest("2.0","/search?q=" + "SELECT solution_data,is_solution FROM messages WHERE is_solution = true limit ${solutions_count}"?url).data.items />

    <#list solutions as solution>
     <#if solution.solution_data.time?long &gt; time >
     <#assign time = solution.solution_data.time?long />
     <#assign latest_soution_id = solution.solution_data.message_id />
     </#if>
    </#list>
    ${latest_soution_id}

     Hope it would work for you. 

9 Replies

Sort By
  • Payal's avatar
    Payal
    Director
    8 years ago

    Hi darrenSP,

     

    We can get the last accepted solution via V2 Query by using the following code:

     

    <#assign last_accepted_solution = rest("2.0","/search?q=" + "SELECT solution_data,is_solution FROM messages WHERE is_solution = true"?url).data.items[0]  />

    ${last_accepted_solution.solution_data.message_id}

     

    This will give you the recent message with accepted solution.

     

    Hope this helps!! LMK if this works for you.

     

    Thanks,

    Payal

  • darrenSP's avatar
    darrenSP
    Mentor
    8 years ago

    Hi Payal,

     

    I have tried this but it does not seem to be the latest accepted solution unfortunately.

     

    Any ideas?

  • VikasB's avatar
    VikasB
    Boss
    8 years ago

    Payal    You are fetching only 1 solution_data.  But solution data does not come in already sorted order so I do not think so it would work.  

  • VikasB's avatar
    VikasB
    Boss
    8 years ago

    darrenSP  You can get the solution accepted time as nested in solution_data but you can not use nested parameter in order by.  
    Here you can use a workaround.   Use below approach

    <#assign accepted_solution = rest("2.0","/search?q=" + "SELECT solution_data,is_solution FROM messages WHERE is_solution = true"?url).data.items[0] />

    <#assign time = accepted_solution.solution_data.time?long />
    <#assign latest_soution_id = accepted_solution.solution_data.message_id />

    <#assign solutions_count = rest("2.0","/search?q=" + "SELECT count(*) FROM messages WHERE is_solution = true"?url).data.count />
    <#assign solutions = rest("2.0","/search?q=" + "SELECT solution_data,is_solution FROM messages WHERE is_solution = true limit ${solutions_count}"?url).data.items />

    <#list solutions as solution>
     <#if solution.solution_data.time?long &gt; time >
     <#assign time = solution.solution_data.time?long />
     <#assign latest_soution_id = solution.solution_data.message_id />
     </#if>
    </#list>
    ${latest_soution_id}

     Hope it would work for you. 

  • darrenSP's avatar
    darrenSP
    Mentor
    8 years ago

    I noticed a few issues and sorted them - realised you had edited and fixed them also!

     

    I've removed the one of the rest calls as only one is needed.

    <#assign solutions = rest("2.0","/search?q=" + "SELECT solution_data,is_solution FROM messages WHERE is_solution = true"?url).data.items />
<#assign accepted_solution = solutions[0] />

<#assign time = accepted_solution.solution_data.time?long />
<#assign latest_solution_id = accepted_solution.solution_data.message_id />

<#list solutions as solution>
 <#if solution.solution_data.time?long &gt; time >
 <#assign time = solution.solution_data.time?long />
 <#assign latest_solution_id = solution.solution_data.message_id />
 </#if>
</#list>

${latest_solution_id}

    Thanks for the help!

  • darrenSP's avatar
    darrenSP
    Mentor
    8 years ago

    I see you have changed since the last time I checked.

     

    Why have you added a count and then searched with the limit of that count?

  • VikasB's avatar
    VikasB
    Boss
    8 years ago

    It would fetch all the accepted solution using count limit otherwise you will get only 25 results. Might be last accepted solution does not includes in that list of 25 accepted solutions. 

    I used the limit to cover that thing. 

    Hope it was helpful. 

  • darrenSP's avatar
    darrenSP
    Mentor
    8 years ago

    Ahh I see. I did not know there was a 25 results limit.

     

    Do you see there being a performance issue with this solution if for example there are 50,000 solutions (as an example)?

  • VikasB's avatar
    VikasB
    Boss
    8 years ago

    Hi darrenSP  That's why I called it a workaround, not a solution  :D
    Still, it would fetch 1K entries even you have limit value 1K+.  And 99.9% last accepted solution will also be included in first 1k entries. So do not worry about too many entries.