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

Message Total for Parent and Sub forums

The Dark Wizard

Well-known member
#1
The database only gives the total for each forum, if you query the message total in the database, it only gives you the parent's count not adding in the count for it's sub forums. Is there anyway to get the amount for the parent and the sub forums?
 

Jake Bunce

XenForo moderator
Staff member
#2
This query will return the sum of message counts in node_id 3 and its first-level children:

Code:
SELECT SUM(f.message_count)
FROM xf_forum AS f
LEFT JOIN xf_node AS n ON (n.node_id = f.node_id)
WHERE n.parent_node_id = 3
OR n.node_id = 3;
If you require more depth then I can make a different query, but it gets slightly more complicated.
 

digitalpoint

Well-known member
#3
It could probably be done with an obnoxious query... but the real question is what do you need it for exactly? Why not used the cached numbers that XenForo already manages/has when it displays the values on the normal forum home page (the numbers there include the sub-forums).
 

The Dark Wizard

Well-known member
#4
This query will return the sum of message counts in node_id 3 and its first-level children:

Code:
SELECT SUM(f.message_count)
FROM xf_forum AS f
LEFT JOIN xf_node AS n ON (n.node_id = f.node_id)
WHERE n.parent_node_id = 3
OR n.node_id = 3;
If you require more depth then I can make a different query, but it gets slightly more complicated.
Ill let you know :D! Thanks for that.

It could probably be done with an obnoxious query... but the real question is what do you need it for exactly? Why not used the cached numbers that XenForo already manages/has when it displays the values on the normal forum home page (the numbers there include the sub-forums).
I run a writing/rp community and will be merging two of my sites together in the foreseeable future adding more then 40 games to the site. Games a forum with their needed sub forums, We have been working on this. http://www.waywardinn.com/pages/testwizard

Where are those numbers cached? I wish to be able to display them on my page.
 

Jake Bunce

XenForo moderator
Staff member
#8
This should do it:

Code:
SELECT p.*
FROM xf_post AS p
LEFT JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
LEFT JOIN xf_node AS n ON (n.node_id = t.node_id)
WHERE (n.parent_node_id = 3
OR n.node_id = 3)
AND p.message_state = 'visible' AND t.discussion_state = 'visible'
ORDER BY p.post_date
DESC
LIMIT 0, 1