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

  • 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?

      • 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