Forum Discussion

Akenefick's avatar
Akenefick
Genius
3 years ago

Get A Topic and ALL Replies

Is there a way to get a Topic and all its replies not just the direct 1st level replies.

For example if I do this query with a replies subquery I only get replies to the topic, not replies to replies. I want the entire thread.

[
  {
    "messages":{
      "fields":[
        "replies"
      ],
      "constraints":[
        {
          "id":"370595"
        }
      ],
      "subQueries":{
        "replies":{
          "fields":[
            "subject",
            "body"
          ]
        }
      }
    }
  }
]

 

Thanks

API
  • MattV's avatar
    MattV
    3 years ago

    The hard part will be guaranteeing the messages are grouped by topic. The API doesn't actually guarantee any particular sort order without the ORDER BY clause (and there is no ORDER BY topic).

    But, your best luck is probably to get the topic id's you need from a board (select id from messages where board.id = 'foo' and depth=0).

    Collect those ID's in a string, formatted like '1','2','3','4'

    Then make another rest call SELECT id,subject FROM messages WHERE topic.id IN (topic_ids_collected)

    That might be the closest you get, but again, order isn't guaranteed. You may just have to sort through it in Freemarker, which isn't performant either. 

  • MattV's avatar
    MattV
    Khoros Staff
    3 years ago

    Use topic.id as the constraint instead.

    Using topic.id as the constraint will return the original topic message and all replies to that topic, including replies to replies.

    This also has the added benefit of not needing subqueries. Subqueries, while convenient in code, can actually negatively impact performance. On the backend they actually still make a separate rest call.

    • Akenefick's avatar
      Akenefick
      Genius
      3 years ago

      MattV Thanks that's helpful. Follow up question, is there a way to do this if I want to get multiple topics and all their replies. For example:

      SELECT topic FROM messages WHERE board.id = 'testboard' 

      Is there a way to get the topic subject and body etc. along with all the replies for multiple topics?

      Thanks

      • MattV's avatar
        MattV
        Khoros Staff
        3 years ago

        so, a query like that will get all messages, topics and their replies from a board, but it will be ordered by last post time by default.

        It might help to know what exactly your goal is to determine the best API calls to use.

  • MattV's avatar
    MattV
    Khoros Staff
    3 years ago

    The hard part will be guaranteeing the messages are grouped by topic. The API doesn't actually guarantee any particular sort order without the ORDER BY clause (and there is no ORDER BY topic).

    But, your best luck is probably to get the topic id's you need from a board (select id from messages where board.id = 'foo' and depth=0).

    Collect those ID's in a string, formatted like '1','2','3','4'

    Then make another rest call SELECT id,subject FROM messages WHERE topic.id IN (topic_ids_collected)

    That might be the closest you get, but again, order isn't guaranteed. You may just have to sort through it in Freemarker, which isn't performant either. 

    • Akenefick's avatar
      Akenefick
      Genius
      3 years ago

      Thanks, this is information I am passing on to another team that wants to pull in Topics from the community, so they won't be using Freemarker. I ended up suggesting something similar.

      SELECT topic.id, subject, body etc... FROM messages 

      Then they will be able to use the topic.id to sort and group everything together on their end.

      I wasn't aware of topic.id being shared by replies. Your first response put me on the right path. Thanks so much for your help.