XF 1.5 SQL Query for Post count in a Specific forum

[LaxmiSathy]

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.

 

[LaxmiSathy]

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

 

[Jake Bunce]

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

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;

 

[Jake Bunce]

2)

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;

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]

@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

 

[LaxmiSathy]

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

 

[Jake Bunce]

1) Revised:

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]

@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.

 

[Jake Bunce]

1) Revised again:

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;