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

Need sql query to find the top poster in a node

Discussion in 'XenForo Questions and Support' started by Renegade, Jun 15, 2013.

  1. Renegade

    Renegade Well-Known Member

    I can understand basic queries but I rather not write them. So can someone please let me know the query that I can run to find out the top 10 posters in a node and another to find the top thread starter in a node.

    I need to run it only once so I will be doing it from phpmyadmin.

    Thank you :love:
     
  2. Renegade

    Renegade Well-Known Member

    Come on, so many stalwarts in here and nobody to help me out with the query. :cry:
     
  3. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Code:
    SELECT u.username, COUNT(*) AS totalPosts
    FROM xf_post AS p
    LEFT JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
    LEFT JOIN xf_user AS u ON (u.user_id = p.user_id)
    WHERE t.node_id = 4
    GROUP BY p.user_id
    ORDER BY totalPosts
    DESC;
    
    Screen shot 2013-06-16 at 11.38.27 AM.png
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Code:
    SELECT u.username, COUNT(*) AS totalThreads
    FROM xf_thread AS t
    LEFT JOIN xf_user AS u ON (u.user_id = t.user_id)
    WHERE t.node_id = 4
    GROUP BY t.user_id
    ORDER BY totalThreads
    DESC;
    
    Screen shot 2013-06-16 at 11.41.17 AM.png
     
    Adam Howard, wedgar and Renegade like this.
  5. Renegade

    Renegade Well-Known Member

    Awesome! Thanks Jake. Of help always :coffee:
     
  6. wmnitin

    wmnitin Member

    Hello,

    if i use WHERE FROM_UNIXTIME (`post_date`) BETWEEN '20140101' AND '20140303'

    It is showing some error like ambiguous, I think error is because post_date column is present on both table xf_thread & xf_post.

    Please provide me correct code for same ! (Need to calculate post count based on time)
     
  7. AndyB

    AndyB Well-Known Member

    I use this website to determine the Unix timestamp:

    http://www.epochconverter.com/

    One the Unix timestamp is determined, you can do this:

    Code:
    SELECT xf_user.username, COUNT(*) AS totalPosts
    FROM xf_post
    LEFT JOIN xf_thread ON xf_thread.thread_id = xf_post.thread_id
    LEFT JOIN xf_user ON xf_user.user_id = xf_post.user_id
    WHERE xf_thread.node_id = 5
    AND xf_post.post_date >= 1388534400
    AND xf_post.post_date < 1393632000
    GROUP BY xf_post.user_id
    ORDER BY totalPosts DESC
    
     
    wmnitin likes this.
  8. wmnitin

    wmnitin Member

    Is it xf_user.username or xf_post.username ? I think some mistake
     
  9. AndyB

    AndyB Well-Known Member

    xf_user.username

    Did you run the query in phpMyAdmin? Be sure to change the xf_thread.node_id as needed.
     
    wmnitin likes this.
  10. wmnitin

    wmnitin Member

    Yeah Worked ! Thanks .
     

Share This Page