Forum Discussion

keithkelly's avatar
2 years ago

Quickest way to get "unanswered topic count" in a board list?

Given the scope "board" what's the best practice to grab the # of topics without replies?

another rest() call?  Or is there some sort of magic I can use in the LIQL query to grab this?

See

${board.UNANSWERED_TOPICS_COUNT}

in the context below:

 

<#assign resp = liql("select title, id, description, depth, href, view_href, node_type, views, topics, messages.count(*), topics.count(*) from boards WHERE parent_category.id='${cat.id}' LIMIT 5000") />

  <#list resp.data.items as board>
       
    <#assign replies_nr = rest("/boards/id/" + board.id + "/replies/count").value?number />   
    <#assign board_subs_nr = rest("/boards/id/" + board.id + "/subscribers/email/board/count").value?number /> 
    <#assign board_scope = restadmin("/boards/id/" + board.id + "/settings/name/custom.dc_scope").value />

    
    <tr class="ind-${board?index}">
      <td>${cat.title}</td>
      <td><a href="${board.view_href}" target="_blank">${board.title}</a></td>
      <td>${board.topics.count}</td>
      <td>${replies_nr}</td>
      <td>${board.messages.count}</td>
      <td>${board.UNANSWERED_TOPICS_COUNT}</td>
      <td>${board_subs_nr}</td>
      <td>${board_scope}</td>
      <td>${board.id}</td>
    </tr>

    
  </#list>

 

 

  • keithkelly  You can try this - 

    liql("Select count(*) from messages where replies.count(*)=0 and depth=0 and board.id='[BOARDID]'").data.count

     

    f you appreciate my efforts, please give me a kudo ↓
    Accept as solution to help others find it faster.

5 Replies

  • keithkelly  You can try this - 

    liql("Select count(*) from messages where replies.count(*)=0 and depth=0 and board.id='[BOARDID]'").data.count

     

    f you appreciate my efforts, please give me a kudo ↓
    Accept as solution to help others find it faster.

  • keithkelly's avatar
    keithkelly
    Leader
    2 years ago

    That did the trick.  Additional Q : Is LIQL() less taxing on the system than REST()?

    (I'm drawing a conclusion based on non-Khoros usages of similar terms)

  • Using liql is more convenient than rest. liql enables you to make a LiQL call with a single argument.

    Here is the comparison between the two - 

    • rest("2.0","/search?q=" + "Select count(*) from messages where replies.count(*)=0 and depth=0 and board.id='[BOARDID]"?url)
    • liql("Select count(*) from messages where replies.count(*)=0 and depth=0 and board.id='[BOARDID]'")

     

     

  • keithkelly's avatar
    keithkelly
    Leader
    2 years ago

    Is the performance better?  (roughly akin to executing SQL directly, rather than through an API)?

  • kkritika's avatar
    kkritika
    Mentor
    2 years ago

    I believe it doesn't make any difference in the performance.