Forum Discussion

grahamgatus's avatar
grahamgatus
Guide
10 years ago

Selecting messages via LiQL with featured conversations ordered first

I am trying to call the REST v2 API, and select all topic messages, with featured conversations prioritised above other messages, ordered by post date.

 

Does anyone know if this is possible to achieve with a single API call?

 

The query would be something along the lines of the below, however in Studio this fails with an error indicating "Field 'conversation.featured' does not exist for object 'messages' as a sort constraint."

 

"select * from messages where depth = 0 order by conversation.featured ASC, post_time DESC"

  • OlivierS's avatar
    OlivierS
    Lithium Alumni (Retired)
    10 years ago

    grahamgatus per the documentation, I don't think this is possible with a SELECT statement:

     

    featured

    Whether or not the message has been selected to be featured using the Feature this Topic option from the Topic Options menu in the community.

    Note: This property is only returned when it is included in a partial response query. It will not appear in a statement such as SELECT * from messages.

    		 boolean

     

    You would need to do one call

     

    select id,conversation.featured from messages where depth=0

     

    Loop through the results. Test if conversation.featured='true'. And do another call to get the needed information from the message:

     

    select <the_fileds_you_want> from messages where id='<the_id>'

     

    Note: I haven't tested the workaround and can't guarantee this is the best option

    • grahamgatus's avatar
      grahamgatus
      Guide
      10 years ago

      I've raised a support iicket and they have also mentioned it is not possible.

       

      For now, the solution I am going with is to build an Endpoint, and perform the 2 queries there (one to fetch featured messages, and one to fetch non featured messages), and merge the 2 result sets together, withfeatured messages ordered above the non featured.

       

      Creating an Endpoint was trivial, and was up and running in an hour or so :smileyhappy:

       

      My requirements have now expanded, and I also need to fetch the avatar for the author of each message, so I need to use an endpoint anyway to be able to condense multiple queries down into a single URL.