Message Total for Parent and Sub forums

[The Dark Wizard]

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]

This query will return the sum of message counts in node_id 3 and its first-level children:

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]

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]

This query will return the sum of message counts in node_id 3 and its first-level children:

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

 

[The Dark Wizard]

Is there a way to display the latest message for the parent and it's first level children?

 

[Jake Bunce]

Is there a way to display the latest message for the parent and it's first level children?

The latest post or latest thread?

 

[The Dark Wizard]

The latest post or latest thread?

Latest post

 

[Jake Bunce]

Latest post

This should do it:

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