Forum Discussion

sejago's avatar
sejago
Guide
4 years ago

Querying Replies In A Date/Time Range

Hi!

New to Khoros so just trying to figure out a couple of things with their API and querying...

I am looking to see who has replied to any post in a given time frame so I have:

SELECT id, replies, conversation.last_post_time FROM messages WHERE conversation.last_post_time > 2021-02-10T18:00:45.846Z AND conversation.last_post_time < 2021-02-10T19:00:45.846Z AND depth = 0

This gives me an array of replies which is fine but do I need to then get the ID of that message and look it up to get the user ID and then look the user up? 

I am thinking there has to be a better way I can do this with the query but am having trouble getting success. Can anyone point me in the right direction?

Thanks,!

  • MattV's avatar
    MattV
    4 years ago

    Try adding author to your select statement. You can also get other author attributes, such as the avatar by adding author.avatar to the select statement.

  • MattV's avatar
    MattV
    4 years ago

    The depth=0 in your query is actually going to get you root posts. If you want only replies you want depth!=0

8 Replies

Sort By
  • MattV's avatar
    MattV
    Khoros Staff
    4 years ago

    Try adding author to your select statement. You can also get other author attributes, such as the avatar by adding author.avatar to the select statement.

  • sejago's avatar
    sejago
    Guide
    4 years ago

    As usually happened with these things...I spent 2 hours trying to figure it out before I posted here and just after I posted I figured it out...I could add author.email and it got me what I needed.

     

    Thanks Matt!

  • sejago's avatar
    sejago
    Guide
    4 years ago

    Actually MattV I noticed something else related to this question that seems like there should be a more efficient way of doing...

    In the query above I mention 'replies' but it seems to give me the message the reply was in and instructs me to query something like:

    "SELECT * FROM messages WHERE parent.id = '3344'"

    Is there no way to directly get replies that occurred in a time frame or do I have the query right and it is supposed to tell you where the reply took place and then you query for replies and iterate through replies to see which ones happened in your date/time range?

  • sejago's avatar
    sejago
    Guide
    4 years ago

    I think I figured it out by using something like this:

     

    SELECT id, replies, author.email FROM messages WHERE post_time > 2021-02-11T00:00:16.131

    The thing is now that it seems to returns all 'messages' ie topics and replies and I can't figure out a way to differentiate between the two

     

  • MattV's avatar
    MattV
    Khoros Staff
    4 years ago

    The depth=0 in your query is actually going to get you root posts. If you want only replies you want depth!=0

  • sejago's avatar
    sejago
    Guide
    4 years ago

    Ah I see! It defaults to 0 if it is not included. That did the trick, thanks again!

  • TedV's avatar
    TedV
    Khoros Alumni (Retired)
    4 years ago

    Hi sejago ,

     

    If the answer solved your question, would you mind marking it as an accepted solution?

     

    Thanks!

  • sejago's avatar
    sejago
    Guide
    4 years ago

    Yep, I already had marked the two answers as 'solved'