useful xenforo queries

Discussion in 'XenForo Discussions' started by Brandon, Jul 17, 2012.

  1. Brandon

    Brandon Regular Member

    Joined:
    Jun 1, 2009
    Messages:
    6,602
    Likes Received:
    1,707
    Location:
    Topeka, Kansas
    First Name:
    Brandon
    I'm going to start playing around with SQL queries, trying to display particular data in a side block or on a new page and here are a few if anyone is interested in adding this to their forums as well.

    Posts per day in a forum over period of time:
    Code:
    SELECT COUNT(*) AS posts, FROM_UNIXTIME(post.post_date, '%Y-%m-%d') AS date
    FROM xf_post AS post
    LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
    WHERE thread.node_id = 10
    AND post.post_date BETWEEN UNIX_TIMESTAMP('2001-01-15 00:00:00') AND UNIX_TIMESTAMP('2001-02-15 00:00:00')
    GROUP BY date
    ORDER BY date
    Likes per day in a forum over period of time:

    Code:
    SELECT COUNT(*) AS likes, FROM_UNIXTIME(liked_content.like_date, '%Y-%m-%d') AS date
    FROM xf_liked_content AS liked_content
    LEFT JOIN xf_post AS post ON (post.post_id = liked_content.content_id)
    LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
    WHERE liked_content.content_type = 'post'
    AND thread.node_id = 10
    AND liked_content.like_date BETWEEN UNIX_TIMESTAMP('2001-01-15 00:00:00') AND UNIX_TIMESTAMP('2001-02-15 00:00:00')
    GROUP BY date
    ORDER BY date
    Threads per day in a forum over period of time:

    Code:
    SELECT COUNT(*) AS threads, FROM_UNIXTIME(thread.post_date, '%Y-%m-%d') AS date
    FROM xf_thread AS thread
    WHERE thread.node_id = 10
    AND thread.post_date BETWEEN UNIX_TIMESTAMP('2001-01-15 00:00:00') AND UNIX_TIMESTAMP('2001-02-15 00:00:00')
    GROUP BY date
    ORDER BY date
    [Source...]

    and then one I put together last night, showing users with the most points (MyPoints)

    Code:
    SELECT `username` , `mypoints_currency`
    FROM `user`
    ORDER BY `user`.`mypoints_currency` DESC
    LIMIT 0 , 10

    Has anyone tried showing this data in a side block or on a new node page, any tips for those looking?

    Thanks
     

Share This Page