List best posts forum-wide based on likes

Data & reporting
1

I’m looking for a way for me, as admin, to see a list of the most popular posts, forum-wide, based on the number of likes.

Basically a posts-centered version of…

My use case is that I’d like to identify posts that were judged most valuable by the community, so I can use them the basis for articles I write for readers of my email newsletter who are not on the forum.

2 Likes
2

I use the following query in the Data Explorer plugin:

SELECT p.id as post_id, p.like_count
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN users u ON u.id = p.user_id
WHERE NOT u.blocked
ORDER BY p.like_count DESC, p.created_at ASC
LIMIT 10

Also, consider using “score” rather than like count. Score takes a weighted average of different measures of post value.

6 Likes
3

Very cool. Thank you, Chris. I will try that.

6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.