Forum Discussion

anmi's avatar
anmi
Adept
3 years ago

Select labels along with messages in one query

I query messages with 

SELECT id, subject, search_snippet, body, cover_image.view_href,
teaser, view_href, author, conversation.last_post_time, replies.count(*),
metrics.views FROM messages WHERE category.id = 'DE' AND conversation.style
= 'tkb' AND body MATCHES 'mykeyword' AND depth = 0 ORDER BY post_time
DESC LIMIT 10
 
To get the labels of each message i currently do an additional labels query for each message.
 
Is there a way to get the labels along with the messages in one query?
Community API
Rest API
  • Akenefick's avatar
    Akenefick
    3 years ago

    I found this:

    IN() Operator Syntax
    "fieldname": { "in": [ "value1", "value2", "value3" ] }

    Example:
    "board.id":{ "in":[ "stereos", "televisions" ] }

    So I think yours would look like this:

    "labels.text":{ "in":[ "Text1", "Text2" ] }
  • Akenefick's avatar
    Akenefick
    Genius
    3 years ago

    Yes you can do this with a json subquery. The Community API v2 request (khoros.com)

    I think yours would look something like this:

    [
    {
      "messages":{
        "fields":[
          "id",
          "subject",
          "search_snippet",
          "body",
          "cover_image.view_href",
          "teaser",
          "view_href",
          "author",
          "conversation.last_post_time",
          "replies.count(*)",
          "metrics.views",
          "labels"
        ],
        "constraints":[
          {
            "category.id":"DE",
            "conversation.style":"tkb",
            "body":"mykeyword",
            "depth":0
          }
        ],
        "sorts": [
            "post_time desc"
        ],
        "limit":10,
        "subQueries":{
          "labels":{
            "fields":[
              "text"
            ]
          }
        }
      }
    }
]

     

    There is a good example here of using Freemarker to make an API request with a json body restBuilder (khoros.com)

     

    • anmi's avatar
      anmi
      Adept
      3 years ago

      Thanks for your answer, 

      unfortunately I cannot access developer.khoros.com urls. They always stuck in a TOO_MANY_REDIRECTS error by the browser.

      Can you please give me an example here on how the request for messages with a subquery looks like?

      • Akenefick's avatar
        Akenefick
        Genius
        3 years ago

        Sure are you using Freemarker? See below.

        <#assign messagePostCall = restBuilder()
   .method("POST")
   .path("/api/2.0/search")
   .body([
    {
      "messages":{
        "fields":[
          "id",
          "subject",
          "search_snippet",
          "body",
          "cover_image.view_href",
          "teaser",
          "view_href",
          "author",
          "conversation.last_post_time",
          "replies.count(*)",
          "metrics.views",
          "labels"
        ],
        "constraints":[
          {
            "category.id":"DE",
            "conversation.style":"tkb",
            "body":"mykeyword",
            "depth":0
          }
        ],
        "sorts": [
            "post_time desc"
        ],
        "limit":10,
        "subQueries":{
          "labels":{
            "fields":[
              "text"
            ]
          }
        }
      }
    }
])
   .admin(true) />
<#assign resp = messagePostCall.call() />

         

        I think that should work.