Forum Discussion

ronaksomani's avatar
ronaksomani
Mentor
10 years ago

How to show highest Kudos posts using API2?

Hi,

I want to show most active posts based on either number of kudos or views. I found a way to implement based on number of replies. But could not find similar approach for kudos/views. 

For replies : SELECT * FROM messages Where depth=0 ORDER BY replies.count(*) DESC

 

I need approach using API2 (LiQl). Can any one help?

  • VarunGrazitti's avatar
    VarunGrazitti
    10 years ago

    ronaksomani - You can do it either way,

     

    Based on the number of Kudos:

    SELECT * FROM messages WHERE kudos.sum(weight)>0 AND depth = 0 ORDER BY kudos.sum(weight) DESC

Based on the number of views:

    SELECT * FROM messages WHERE depth = 0 ORDER BY metrics.views DESC

     

5 Replies

Sort By
  • VarunGrazitti's avatar
    VarunGrazitti
    Boss
    10 years ago

    ronaksomani - You can do it either way,

     

    Based on the number of Kudos:

    SELECT * FROM messages WHERE kudos.sum(weight)>0 AND depth = 0 ORDER BY kudos.sum(weight) DESC

Based on the number of views:

    SELECT * FROM messages WHERE depth = 0 ORDER BY metrics.views DESC

     

  • ronaksomani's avatar
    ronaksomani
    Mentor
    10 years ago

    VarunGrazitti Thank you for the response.

    Is there any way I can retrieve most active post based on combination of (replies / kudos / views)?

  • VarunGrazitti's avatar
    VarunGrazitti
    Boss
    10 years ago

    ronaksomani  - Yes, you can do that, you can sort them based upon any one factor, either views, kudos or replies. In the following query, I've sorted it by views and included the kudos and replies as well.

     

    SELECT * FROM messages WHERE kudos.sum(weight)>0 AND replies.count(*) > 0 AND depth = 0 ORDER BY metrics.views DESC

     

    I hope this helps.

  • ronaksomani's avatar
    ronaksomani
    Mentor
    10 years ago

    VarunGrazitti  Above query will filter posts having kudos and replies greater than 0, and will order them  by views count. But I am looking for a query (if possible), where I can ORDER BY combination of (replies/kudos/views). Thus I will get a response which will have most active posts.

  • VarunGrazitti's avatar
    VarunGrazitti
    Boss
    10 years ago

    ronaksomani -  Well, you need to formulate such query, I wouldn't say that can't be done, but what I can think of now is that you cannot combine or rather add up these factors. What you meant is something like S(ort) = K(udos)+R(eplies)+V(iews) for each post and then sort based on the sum of these, right? 

     

    Please note that the best factor to get the most active posts is the number of views in most of the cases, because people does not always give Kudos to the posts or reply too often, but if you have views, your post is reachable. e.g. In the below SS, the post circled in red has only 11 replies but 9 Kudos and has 70 Views (checked from API).

     

    Example

     

    The post in green has 25 Replies but only 4 Kudos, and has 70 Views as well. The post in Blue has only 5 Replies and 1 kudo, but has 55 Views, so putting it in the formula we have above:

     

    Sort = K + R + V

     

    Red => 9 + 11 + 70 = 90

    Blue => 1 + 5 + 55 = 61

    Green => 4 + 25 + 70 = 99

     

    So, if you go by the above formula, you should get the Green post on the top, then Red and Blue, but also note that more views don't always mean more kudos or more replies, 55 people viewed blue but only one kudoed it and 5 replied. It could have been a case where lets say the blue post had 100 views and same kudos and replies, then your score for Blue would have been 106, means it would top the list, so you see that Replies would be the best factor to consider in case you want to show most viewed list.

     

    For this current thread, it has only 5 Replies + 0 Kudos + 56 Views and also has a solved answer, moreover, like lithium has hot topics based on only number of replies, so you can consider that as well. Remember, users will always read and scroll through your posts but won't always kudo or reply.

     

    I hope this helps.