Forum Discussion

vishwajeet_hol's avatar
vishwajeet_hol
Expert
11 years ago

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

Hi,   I want six solutions for a given category or board, for the last 30 days, ordered by number of views.   I found one rest API V1 call  :   http://<community>/restapi/vc/categories/id/<categ...
VarunGrazitti's avatar
VarunGrazitti
Boss
11 years ago

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.

  • vishwajeet_hol's avatar
    vishwajeet_hol
    Expert
    11 years ago

    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.