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. 

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