Forum Discussion

wpigoury's avatar
wpigoury
Mentor
10 years ago

API v2 _ Problem with ORDER BY

Hi,

 

I wanted to play a little with LiQL requests and tried to execute this request I found in the API v2 documentation:

SELECT id, topics.count(*), messages.count(*), views FROM boards ORDER BY views DESC

 

But I get this error:

{
  "status" : "error",
  "message" : "invalid query syntax for SELECT id,topics.count(*),messages.count(*),views FROM boards ORDER BY views DESC",
  "data" : {
    "type" : "error_data",
    "code" : 604,
    "developer_message" : "Field 'views' does not exist for object 'boards'. in: SELECT id,topics.count(*),messages.count(*),views FROM boards ORDER BY views DESC",
    "more_info" : ""
  },
  "metadata" : { }
}

 I also tried to switch views for id which should be a valid field but I still get the error when using ORDER BY (field 'id' does not exist):

SELECT id, topics.count(*), messages.count(*) FROM boards ORDER BY id DESC

 

Any idea what the problem might be?

 

Thanks.

  • AlexW's avatar
    AlexW
    10 years ago

    The top of the document mentioned that the features are added in 15.1. Make sure your community have upgraded to 15.1.

     

    But for the use case mentioned, you should be able to do something like this in previous versions:

     

    SELECT * FROM messages where category.id = '<category>' and depth=0 ORDER BY post_time DESC

     

    Hope this helps.

6 Replies

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

    wpigoury 

     

    Where did you find the query? 

     

    Just doing a simple query, it appears that there isn't any 'views' field in the result:

     

    SELECT * FROM boards

     

    It could have been a typo, but it looks like we don't support aliases as this is failing too:

     

    SELECT id, topics.count(*), messages.count(*) AS views FROM boards ORDER BY views DESC

     

     

  • wpigoury's avatar
    wpigoury
    Mentor
    10 years ago

    I got this query from the documentation, right here: http://community.lithium.com/t5/Community-API-v2/Board-resource/ta-p/184330#liqlOptions

    'views' is stated as a valid board property and the very same query is given in the sample queries at the bottom of the page.

     

    I didn't manage to get ORDER BY working with any property.

     

    My aim is to retrieve all contests from a given category sorted by posting_date_start from most recent to older with a pagination.

    It seems impossible to do with API v1, I was hoping API v2 LiQL would be more useful for this kind of query.

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

    The top of the document mentioned that the features are added in 15.1. Make sure your community have upgraded to 15.1.

     

    But for the use case mentioned, you should be able to do something like this in previous versions:

     

    SELECT * FROM messages where category.id = '<category>' and depth=0 ORDER BY post_time DESC

     

    Hope this helps.

  • wpigoury's avatar
    wpigoury
    Mentor
    10 years ago

    That must be it, the community is not yet on 15.1.

    Pretty confusing as some queries work and some others don't.

    Maybe it would be a good idea to tell which feature has been integrated in which version.

    Right now it gives the impression that all Board properties are only available on 15.1.

     

    So I guess I have to wait a little, 15.1 should come soon.

     

    Thanks for the quick answer anyway.

  • wpigoury's avatar
    wpigoury
    Mentor
    10 years ago
    As for the messages query, it's not what I'm looking to achieve.
    I don't want to retrieve messages but contest boards.
    We have a page listing all our contests and right now I have to load all of them and then reverse the result which is not optimal.