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

      • anmi's avatar
        anmi
        Adept
        3 years ago

        thx,

        the like contraint doesnt seem to work:

        "body":"mykeyword",

        it returns 

        "message": "Invalid query syntax",