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 i...
  • 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. 

Payal's avatar
Payal
Director

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

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.