Return six solutions for a given category or board, for the last 30 days, ordered by number of views

vishwajeet_hol - Using V2, you can use this to fetch the results and sort by views.

SELECT * FROM messages WHERE category.id IN ('CATEGORY_ID') AND is_solution = true ORDER BY metrics.views DESC LIMIT 6

Regarding the date range, V2 allows only to sort by message post time with depth 0, e.g.

SELECT subject, id FROM messages WHERE conversation.last_post_time > 2013-10-07T10:04:30-08:00 
AND conversation.last_post_time < 2013-11-07T10:04:30-08:00 AND depth=0 
ORDER BY conversation.last_post_time DESC

More info here. I hope this helps.

Hi VarunGrazitti,

Thanks !!!

Now, I am able to fetch the results and sort by views using given query.

SELECT * FROM messages WHERE category.id IN ('CATEGORY_ID') AND is_solution = true ORDER BY metrics.views DESC LIMIT 6

To use date range, i tried this:

SELECT * FROM messages WHERE category.id IN ('CATEGORY_ID') AND conversation.last_post_time > 2015-03-16T10:04:30-08:00
AND conversation.last_post_time < 2015-07-16T10:04:30-08:00 AND depth=0 AND is_solution = true ORDER BY metrics.views DESC LIMIT 6

But unable to get the required respose, because when we say is_solution = true then we can't satisfy the codition depth=0.

So how to use date range in above query.

Regards,

Vishwajeet.

VarunGrazitti Thanks for information !!!

Hi VarunGrazitti,

Used below code given by you :

SELECT * FROM messages WHERE category.id IN ('CATEGORY_ID') AND is_solution = true ORDER BY metrics.views DESC LIMIT 6

It gives six solutions in that category ordered by no. of views.

Suppose, I want only one solution per topic.

That meance if a post has multiple solutions,I want only a single latest solution.

I think we have to use Distinct topic.id for this.

Any idea about how to implement it.

Thank you.

Regards,

Vishwajeet

Hi VarunGrazitti,

2 years ago ;-), was you able to implement it?

I need something similar, latest 5-7 solutions fro a specific board, sorted by acceptance date. 

And show subject, some body content and acceptance date.

Any code samples appreciated.