SQL Query Help - Most Likes / Liked Post In Specific Thread

[ProCom]

We often do contests where we use the "like" system to have members vote for the post they like the best within the entire thread.

Sometimes the contests can have hundreds of submissions which make it very difficult to go through and find the post with the most likes.

I don't need an addon... just a quick and easy SQL command to let me pull the data.

@Jake Bunce did a great job doing a query for "most likes within a node" here: http://xenforo.com/community/threads/most-liked-posts-in-a-single-node-in-the-last-month.21893/

... but I need most likes within a specific thread ID. Anybody know which query I should run (assuming the ID for the thread is 1234)

Thanks!

 

[Jake Bunce]

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.thread_id = 9759
GROUP BY lc.content_id
ORDER BY likes
DESC;

 

[ProCom]

Again I find myself bowing down to @Jake Bunce !

It worked perfectly, thanks!

Now, a quick follow up if you don't mind: Say I have a post that has the highest likes and it is post_id # 1234567, how do I create a URL / link directly to that post? For example, if the thread URL is:

http://www.mydomain.com/threads/contest.9759/

Is there a way to link directly to the post_id 1234567 in that thread?

 

[Paul B]

Your post above is: http://xenforo.com/community/posts/692634

Use the /posts route.

 

[ProCom]

Ugh, so brilliant, thanks @Brogan !

I'm loving xF more and more with each passing post!