XF 1.1 Most liked posts in a single node in the last month

[jgas]

On my board, we run a monthly contest, where the most voted song is elected "song of the month".

I want to use the like system to count the votes, so I need to know if it is possibile to search for the "most liked posts" in the latest x days, for a single node/category/forum.

Many thanks!

 

[Paul B]

You can filter threads by first message likes, but not individual posts.

 

[Jake Bunce]

Run this query on your database:

SELECT COUNT(*) AS likes, t.title, p.post_id
FROM xf_liked_content AS lc
LEFT JOIN xf_post AS p ON (p.post_id = lc.content_id)
LEFT JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
WHERE lc.content_type = 'post'
AND t.node_id = 10
GROUP BY lc.content_id
ORDER BY likes
DESC;

You will get results like this:

 

[Jake Bunce]

Oh, you want a time period:

SELECT COUNT(*) AS likes, t.title, p.post_id
FROM xf_liked_content AS lc
LEFT JOIN xf_post AS p ON (p.post_id = lc.content_id)
LEFT JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
WHERE lc.content_type = 'post'
AND t.node_id = 10
AND lc.like_date BETWEEN UNIX_TIMESTAMP('2010-01-15 00:00:00') AND UNIX_TIMESTAMP('2010-02-15 00:00:00')
GROUP BY lc.content_id
ORDER BY likes
DESC;

 

[jgas]

Thanks jake, you really know the answers!
Since it's a work that i need to do every month, or even more if i want some stats about the "likeness" of my posts, it would be great to have a page where i can choose the node and the period of time.
Is it hard to code a page like this? Who can help me?

 

[jgas]

You can filter threads by first message likes, but not individual posts.

By the way, this is interesting too. How can i do it?

Edit : i found it out :it's /n the thread display option on the bottom of every threadlist page!

 

[Barbossa]

Oh, you want a time period:

SELECT COUNT(*) AS likes, t.title, p.post_id
FROM xf_liked_content AS lc
LEFT JOIN xf_post AS p ON (p.post_id = lc.content_id)
LEFT JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
WHERE lc.content_type = 'post'
AND t.node_id = 10
AND lc.like_date BETWEEN UNIX_TIMESTAMP('2010-01-15 00:00:00') AND UNIX_TIMESTAMP('2010-02-15 00:00:00')
GROUP BY lc.content_id
ORDER BY likes
DESC;

Jake, how bout, most liked user in a single/all node in the last month? Thank you.

 

[Blue]

I would like to be able to list the the most liked user / topic for the whole forum in the last 30 days.

Very useful, thanks Jake.