Forum Discussion

vbandi's avatar
vbandi
Contributor
9 years ago

Get replies counts along with Topic

Hi All,

 

I was trying to get count of replies  along with Root Topic. (Or count of child items when i am trying to retrieve parent)

 

For example :-

To get root Topic

i have used LiQL as below(modified for readability)

http://test/api/2.0/search?q=SELECT+*+FROM+messages+WHERE+board.id= 'tp' +AND+depth=0+and+conversation.style='forum'+LIMIT+20+OFFSET+0

 

This query returns the root topics.

I also want to know how many replies are present for this topic.For example also get replies.count(*).

 

I cannot combine replies.count(*) in above query .How can i get both root topic and count of its replies in a single LiQL query.

 

Could someone help me on this.

 

Thanks,

Vijay

 

  • ChhamaJ's avatar
    ChhamaJ
    9 years ago

    Hi vbandi,

     

    You cannot combine  '*' and any other field in the LiQL query. If you need to get replies.count(*) along with other fields then you will have to specify all the fields individually.  For example, 

    Select id, board, subject, author, replies.count(*) from messages where board.id = 'tp' and depth=0

    In the above query, add all the fields that you need to be returned.

     

    Hope this helps.

     

    Regards,
    Chhama

     

6 Replies

Sort By
  • VarunGrazitti's avatar
    VarunGrazitti
    Boss
    9 years ago

    vbandi - Remove the depth = 0

     

    http://test/api/2.0/search?q=SELECT+*+FROM+messages+WHERE+board.id= 'tp' +AND+and+conversation.style='forum'+LIMIT+20+OFFSET+0

     

    I hope this helps.

  • vbandi's avatar
    vbandi
    Contributor
    9 years ago

    thanks a lot for the reply VarunGrazitti

     I wanted to have some thing like this

     

    http://test/api/2.0/search?q=SELECT+* , replies.count(*) +FROM+messages+WHERE+board.id= 'tp' +AND+depth=0+and+conversation.style='forum'+LIMIT+20+OFFSET+0

     

    The intention of having this query is to get root topic and get count of child topics under it to display it to user.

     

    I think VarunGrazitti, the query u mentioned might not be helpful to get the required result.

    .

     

    Thanks,

    Vijay

     

  • ChhamaJ's avatar
    ChhamaJ
    Khoros Staff
    9 years ago

    Hi vbandi,

     

    You cannot combine  '*' and any other field in the LiQL query. If you need to get replies.count(*) along with other fields then you will have to specify all the fields individually.  For example, 

    Select id, board, subject, author, replies.count(*) from messages where board.id = 'tp' and depth=0

    In the above query, add all the fields that you need to be returned.

     

    Hope this helps.

     

    Regards,
    Chhama

     

  • santosh_csc's avatar
    santosh_csc
    Guide
    8 years ago
    Select conversation.count(*) from messages where board.id = 'tp' and depth=0 should work right?
  • VikasB's avatar
    VikasB
    Boss
    8 years ago

    Hi santosh_csc can I know what you want to get?

    I think you are trying to get the topics or message count excluding replies. If yes, you can use below API

     

    Select count (*) from messages where board.id = 'tp' and depth = 0 

  • santosh_csc's avatar
    santosh_csc
    Guide
    8 years ago
    VikasB,
    That worked. Instead of making 2 calls to get count and other data, is it possible to get message ID also along with the count?

    So that I don't have to do 2 calls for getting count and actual messages.