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