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
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.
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
