Forum Discussion

sullysnack's avatar
sullysnack
Khoros Alumni (Retired)
9 years ago

APIv2 Query for User's Topics Participated In?

 

Hi Lithium Developers,

 

It looks like LiQL aka APIv2 won't allow a single query which searches for all unique topics in which a user has participated. Can you confirm?

 

We can get a list of all of the user's posts aka topic starters and replies in one list:

SELECT * FROM messages WHERE author.id = "101" ORDER BY post_time DESC

 

We can get a list of the user's topic starters, sorted by conversation.last_post_time:

SELECT * FROM messages WHERE author.id = "101" AND depth = 0 ORDER BY conversation.last_post_time DESC

 

But when we remove the depth = 0 restriction...

SELECT * FROM messages WHERE author.id = "101" ORDER BY conversation.last_post_time DESC

We get error:

"invalid query syntax for SELECT * FROM messages WHERE author.id = '101' ORDER BY conversation DESC"

 

 

Is there a way to construct a single query so that it searches for all of a user's posts but gives only the distinct topics, and sort the results by conversation.last_post_time? If the user only posted the topic starter, that's one of the results. If the user posted a topic starter and one or more replies to that topic, that topic is one of the results. If the user did not post the topic starter but posted one or more replies to the topic, that topic is one of the results. No can do? Why not? How does Lithium query for the sorted items it shows in the View Profile Page - Topics Participated In list? Is this possible with one query in API v1?

 

I attempted to duplicate the Topics Participated In behavior in a custom component. For users who have made several hundred or more posts, the master query and my custom code logic cause very lengthy delays loading the view profile page. The logic:

1) Query for *all* of the user's posts. Collect only those posts' conversation ids and conversation last_post_times.

2) In Freemarker, while iterating over all of the posts, discard duplicate conversations.

3) Use Freemarker array sort to sort the unique conversations by their last_post_times.

4) Return a subset of the list for display on a page, e.g. the first 10 aka most recent 10.

This code is inefficient when there are a lot of results.

 

Looking for alternatives.

 

 

Thank you,

Dave

 

 

3 Replies

Sort By
  • YuriK's avatar
    YuriK
    Khoros Expert
    8 years ago

    Hey Dave,

     

    It looks like the issue you ran into stems from not being able to sort on "conversation.last_post_time" without "depth = 0". Would sorting by "post_time" instead be sufficient for your use case? This would get you the messages sorted by the time they were posted.

     

    The following works for me:

     

    SELECT topic.id FROM messages WHERE author.id = "101" ORDER BY post_time DESC

    You would then just have to page through and get enough messages to get 10 topics to display.

     

    I'll double check with the team to see if they have any other suggestions.

     

    Hope this helps,

     

    Yuri

     

     

  • sullysnack's avatar
    sullysnack
    Khoros Alumni (Retired)
    8 years ago

    Thanks Yuri. No, that query would sort the topics by the post times of when the user participated. The value in Lithium's Participated In widget is that the list is sorted by the post times of all of the topics' latest posts, regardless of who posted last to each one. Consider the frequent contributor user who last posted to a topic in November. In December and January the user replied to dozens of other topics. Then today some other user posts to the stale topic from November. In Lithium's widget the topic bubbles to the top of the user's list. But in the query you provided it will remain hidden on the 4th+ page of the user's list.

     

    I see you're aware the query you provided also can list the same topics multiple times - when the user has posted more than once in each topic. With brute force I coded some FM to iterate through all of the user's topics. Do you see why that's necessary given the available queries and the desired sort order? The FM code maintains a list of only the unique topics then sorts all of those by latest post time. Unfortunately when the user has more than ~750 posts, the brute force queries and FM code cause undesired page load delays.

     

     

  • YuriK's avatar
    YuriK
    Khoros Expert
    8 years ago

    Hey Dave,

     

    Sorry, I misread your question when I went through it the first time. I thought it may be sufficient for you to get the posts ordered by when the user in question last contributed to the thread.

     

    Unfortunately, it looks like this may be a gap in our API and we would need to have one of our dev teams take a look. Can you please file a support ticket for tracking?

     

    Thank you,

     

    Yuri