• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

jgas

Active member
#1
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!
 

Jake Bunce

XenForo moderator
Staff member
#3
Run this query on your database:

Code:
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:

Screen shot 2011-10-21 at 1.24.30 PM.png
 

Jake Bunce

XenForo moderator
Staff member
#4
Oh, you want a time period:

Code:
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

Active member
#5
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?
 

Barbossa

Active member
#7
Oh, you want a time period:

Code:
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

Well-known member
#8
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.