Query to find top posters in a specific Node ID

CTXMedia

Formerly CyclingTribe
#1
I wonder if one of you could help me with an SQL query.

I'd like to list all members who have posted in a specific node ID in order of most posts (excluding any deleted posts), e.g.;

Member A - 15659
Member X - 14957
Member J - 8452
Member C - 6695
Member F - 2341

Thanks in advance,
Shaun :D
 

Chris D

XenForo developer
Staff member
#2
Should be something like this (untested) just change the node_id to whatever you want:
Code:
SELECT post.user_id, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC
EDIT: Just updated it to include only valid, not banned users, and a check to ensure the thread is visible.
 

Xon

Well-known member
#4
Should be something like this (untested) just change the node_id to whatever you want:
Code:
SELECT post.user_id, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC
EDIT: Just updated it to include only valid, not banned users, and a check to ensure the thread is visible.
Much faster way:
Code:
SELECT post.user_id, SUM(post.post_count) AS count
FROM xf_thread_user_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE  thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC
This scales on the number of threads/users, rather than posts/threads/users.
 

Breixo

Well-known member
#6
Should be something like this (untested) just change the node_id to whatever you want:
Code:
SELECT post.user_id, COUNT(*) AS count
FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE post.message_state = 'visible'
    AND thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC
EDIT: Just updated it to include only valid, not banned users, and a check to ensure the thread is visible.
Much faster way:
Code:
SELECT post.user_id, SUM(post.post_count) AS count
FROM xf_thread_user_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE  thread.discussion_state = 'visible'
    AND thread.node_id = 123
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC
This scales on the number of threads/users, rather than posts/threads/users.
Hi, thanks for sharing your help.
I find this quite interesting.
Is it possible to do the same but getting the username instead of user_id?

My attempt:

Code:
SELECT post.username, SUM(post.post_count) AS count
FROM xf_thread_user_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.username = user.username)
WHERE  thread.discussion_state = 'visible'
    AND thread.thread_id = 1
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.username
ORDER BY count DESC
But obviously it didn't work for me :(

I already checked this addon from Xon/Fuhrmann
https://xenforo.com/community/resources/who-replied.1537/
But what I need is to be able to export a list of usernames.
Thanks in advance!

PS: well, just made a last search before posting and found this thread.
It helped me to mix both and get the solution that works for me (getting usernames from members who posted in a thread, useful for contests or giveaways):
Code:
SELECT post.user_id, user.username, SUM(post.post_count) AS count
FROM xf_thread_user_post AS post
INNER JOIN xf_thread AS thread ON
    (post.thread_id = thread.thread_id)
INNER JOIN xf_user AS user ON
    (post.user_id = user.user_id)
WHERE  thread.discussion_state = 'visible'
    AND thread.thread_id = 1
    AND user.user_state = 'valid'
    AND user.is_banned = 0
GROUP BY post.user_id
ORDER BY count DESC
 
Top