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
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!