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

XF 1.5 SQL Query for Post count in a Specific forum

Discussion in 'XenForo Questions and Support' started by LaxmiSathy, Jun 8, 2016.

  1. LaxmiSathy

    LaxmiSathy Member

    Hello,
    Am trying to get forum stats and am looking for a sql query to execute wherein,

    1)if I give the node id for the category say "Official Forums" - https://xenforo.com/community/#official-forums.1
    the result should list out, say for a particular Week 22 - 5/30-6/5, the number of posts in the nodes under this category like:


    Node Name Post Count
    ----------------------------------
    Announcements #
    Have you seen...? #
    Frequently asked questions #


    2)For a particular week, Week 22 - 5/30-6/5, in a particular node, say "Announcements" -https://xenforo.com/community/forums/announcements/
    the number of posts i) grouped by thread title ii) grouped by usernames
    ordered by post count descending
    as below

    Thread Title Post Count
    -----------------------------------
    Thread A #
    Thread B #

    User name Post Count
    -----------------------------------
    User A #
    User B #

    Thanks for the help.
     
  2. LaxmiSathy

    LaxmiSathy Member

    @Jake Bunce can you help me out with the sql query request.
    Thanks for the response.
     
  3. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    1) Enter the category's node_id and the date range.

    Code:
    SELECT n.title, COUNT(*) AS 'TotalPosts'
    FROM xf_node AS n
    INNER JOIN (
    	SELECT lft, rgt
    	FROM xf_node
    	WHERE node_id = 21
    ) cat ON (1=1)
    LEFT JOIN xf_thread t ON (t.node_id = n.node_id)
    LEFT JOIN xf_post p	ON (p.thread_id = t.thread_id)
    WHERE cat.lft < n.lft AND n.rgt < cat.rgt
    AND t.discussion_state = 'visible'
    AND p.message_state = 'visible'
    AND p.post_date BETWEEN UNIX_TIMESTAMP('2014-02-23 00:00:00') AND UNIX_TIMESTAMP('2016-05-23 00:00:00')
    GROUP BY n.node_id, n.title;
    
     
    LaxmiSathy likes this.
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    2)

    Code:
    SELECT t.thread_id, t.title, COUNT(*) AS 'TotalPosts'
    FROM xf_node AS n
    LEFT JOIN xf_thread t ON (t.node_id = n.node_id)
    LEFT JOIN xf_post p	ON (p.thread_id = t.thread_id)
    WHERE n.node_id = 1
    AND t.discussion_state = 'visible'
    AND p.message_state = 'visible'
    AND p.post_date BETWEEN UNIX_TIMESTAMP('2014-02-23 00:00:00') AND UNIX_TIMESTAMP('2016-05-23 00:00:00')
    GROUP BY t.thread_id, t.title
    ORDER BY TotalPosts DESC;
    
    Code:
    SELECT p.username, COUNT(*) AS 'TotalPosts'
    FROM xf_node AS n
    LEFT JOIN xf_thread t ON (t.node_id = n.node_id)
    LEFT JOIN xf_post p	ON (p.thread_id = t.thread_id)
    WHERE n.node_id = 1
    AND t.discussion_state = 'visible'
    AND p.message_state = 'visible'
    AND p.post_date BETWEEN UNIX_TIMESTAMP('2014-02-23 00:00:00') AND UNIX_TIMESTAMP('2016-05-23 00:00:00')
    GROUP BY p.username
    ORDER BY TotalPosts DESC;
    
     
    LaxmiSathy likes this.
  5. LaxmiSathy

    LaxmiSathy Member

    @Jake Bunce yeah this works but little tweaking,
    #1)
    - would want them to be ordered by display order of the nodes.
    -ONLY the first level of nodes to be considered when displaying the result
    For example:
    Category 'Main'
    - Node A
    --Node A1
    --Node A2
    --Node A3
    -Node B
    -Node C
    --Node C1
    --Node C2

    then the query result should be

    Main - Post count # ( okay if it is category it is not going to have any post count # but may use the query to pull data from a main forum which has posts)
    Node A - Post count in its node as well its child nodes A1, A2, A3
    Node B - Post count #
    Node C - Post count in its node as well its child nodes C1,C2
     
  6. LaxmiSathy

    LaxmiSathy Member

    @Jake Bunce #2 queries work perfect to my requirement.
     
  7. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    1) Revised:

    Code:
    SELECT n.title, COUNT(*) AS 'TotalPosts'
    FROM xf_node AS n
    LEFT JOIN xf_node AS n2 ON (n.lft < n2.lft AND n2.rgt < n.rgt)
    LEFT JOIN xf_thread t ON (t.node_id = n.node_id OR t.node_id = n2.node_id)
    LEFT JOIN xf_post p ON (p.thread_id = t.thread_id)
    WHERE n.parent_node_id = 21
    AND t.discussion_state = 'visible'
    AND p.message_state = 'visible'
    AND p.post_date BETWEEN UNIX_TIMESTAMP('2014-02-23 00:00:00') AND UNIX_TIMESTAMP('2016-05-23 00:00:00')
    GROUP BY n.node_id, n.title
    ORDER BY n.display_order ASC;
    
     
    LaxmiSathy likes this.
  8. LaxmiSathy

    LaxmiSathy Member

    @Jake Bunce, you are a rock star. Just a little revision, the output to include the post count from the node id 21 as well.
     
  9. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    1) Revised again:

    Code:
    SELECT n.title, COUNT(*) AS 'TotalPosts'
    FROM xf_node AS n
    LEFT JOIN xf_node AS n2 ON (n.lft < n2.lft AND n2.rgt < n.rgt)
    LEFT JOIN xf_thread t ON (t.node_id = n.node_id OR t.node_id = n2.node_id)
    LEFT JOIN xf_post p ON (p.thread_id = t.thread_id)
    WHERE (n.parent_node_id = 21 OR n.node_id = 21)
    AND t.discussion_state = 'visible'
    AND p.message_state = 'visible'
    AND p.post_date BETWEEN UNIX_TIMESTAMP('2014-02-23 00:00:00') AND UNIX_TIMESTAMP('2016-05-23 00:00:00')
    GROUP BY n.node_id, n.title
    ORDER BY n.depth ASC, n.display_order ASC;
    
     

Share This Page