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

Discussion in 'XenForo Questions and Support' started by The Dark Wizard, May 6, 2012.

  1. The Dark Wizard

    The Dark Wizard Well-Known Member

    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?
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
  3. digitalpoint

    digitalpoint Well-Known Member

    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).
     
  4. The Dark Wizard

    The Dark Wizard Well-Known Member

    Ill let you know :D! Thanks for that.

    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.
     
  5. The Dark Wizard

    The Dark Wizard Well-Known Member

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

    Jake Bunce XenForo Moderator Staff Member

    The latest post or latest thread?
     
  7. The Dark Wizard

    The Dark Wizard Well-Known Member

    Latest post :D
     
  8. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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
    
     

Share This Page