Forum Discussion

Akenefick's avatar
Akenefick
Genius
4 years ago

API for board information per time period

We are trying to pull some data about specific boards through the API, and we also want to be able to specify a time period. Is there a way to do the following? Total Posts by Board per defined ti...
API
  • SuzieH's avatar
    SuzieH
    4 years ago

    Hi Akenefick 

    You might have better luck if you use the Community API v2 /search endpoint with a LiQL query that includes a date range in the WHERE clause. I'm not an engineer, but I was able to get what I think are correct responses on my QA site. 

    For Total Posts by Board per defined time period, I tried the following query to the messages collection. Note that I included "(depth=0)" in the WHERE clause. That filters results to topics only. Remove it if you want both topics and replies in the response. Also, be sure to look at the note about using date ranges in LiQL queries when using the post_time field as a constraint. 

     

    select count(*) from messages where board.id = 'suzieForum1' AND depth=0 AND post_time > 2018-10-07T10:04:30-08:00 AND post_time < 2021-07-19T10:04:30-08:00

     

     

    For Top Solution Authors by Board per defined time period, I tried the following. I took "Top" to be 'most kudoed' so that is how I ordered the results. Also, I only returned messages that had 1 or more kudos with "kudos.sum(weight) > 0".  I think you'd want to do something like that so that your response is lighter?

     

    select author, subject, view_href, kudos.sum(weight) from messages where board.id = 'suzieForum1' AND kudos.sum(weight) > 0 AND is_solution = true AND post_time > 2013-10-07T10:04:30-08:00 AND post_time < 2021-07-19T10:18:30-08:00 order by kudos.sum(weight) DESC LIMIT 5

     

     

    I'm not sure how to get Total Members accessing the Board per defined time period. The boards collection has a views field, but I don't think that's unique views, and I don't know of a way to return whether views are by actual community members vs anonymous users. Perhaps someone in Services or Support has done something like this ChadB AdamN TysonN MattV ? Or maybe some of our other Titans Claudius jeffshurtliff allensmith81 ? Hopefully one of these folks can also verify whether my example queries are performant or not 😀.

    Cheers!

     

SuzieH's avatar
SuzieH
Khoros Alumni (Retired)
4 years ago

Hi Akenefick 

You might have better luck if you use the Community API v2 /search endpoint with a LiQL query that includes a date range in the WHERE clause. I'm not an engineer, but I was able to get what I think are correct responses on my QA site. 

For Total Posts by Board per defined time period, I tried the following query to the messages collection. Note that I included "(depth=0)" in the WHERE clause. That filters results to topics only. Remove it if you want both topics and replies in the response. Also, be sure to look at the note about using date ranges in LiQL queries when using the post_time field as a constraint. 

 

select count(*) from messages where board.id = 'suzieForum1' AND depth=0 AND post_time > 2018-10-07T10:04:30-08:00 AND post_time < 2021-07-19T10:04:30-08:00

 

 

For Top Solution Authors by Board per defined time period, I tried the following. I took "Top" to be 'most kudoed' so that is how I ordered the results. Also, I only returned messages that had 1 or more kudos with "kudos.sum(weight) > 0".  I think you'd want to do something like that so that your response is lighter?

 

select author, subject, view_href, kudos.sum(weight) from messages where board.id = 'suzieForum1' AND kudos.sum(weight) > 0 AND is_solution = true AND post_time > 2013-10-07T10:04:30-08:00 AND post_time < 2021-07-19T10:18:30-08:00 order by kudos.sum(weight) DESC LIMIT 5

 

 

I'm not sure how to get Total Members accessing the Board per defined time period. The boards collection has a views field, but I don't think that's unique views, and I don't know of a way to return whether views are by actual community members vs anonymous users. Perhaps someone in Services or Support has done something like this ChadB AdamN TysonN MattV ? Or maybe some of our other Titans Claudius jeffshurtliff allensmith81 ? Hopefully one of these folks can also verify whether my example queries are performant or not 😀.

Cheers!