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

External script - best way to retrieve latest posts on a big board?

Discussion in 'XenForo Development Discussions' started by janslu, Jan 4, 2016.

  1. janslu

    janslu Member

    I'm working on a bridge that will connect Xenforo and my main site. Thanks to wonderful recources available here I have managed to retrieve the list of latest threads, but I also want to display a list of latest posts, limited to specific forums. This would be an easy thing to do, but I'm running a very large board (15 million posts) and I need to take care of performance. And here's my question: what is the best way to query the database for the last 5 posts coming from nodes x,y,z from the performance point of view? Should I employ xenforo models as much as possible, or query the database directly?

    This is the function I'm using to query for threads:
    Code:
    $nodeModel = XenForo_Model::create('XenForo_Model_Node'); // Retrieve the default viewable forums
            if ($nodeids == '') {
                $nodeids = $nodeModel->getViewableNodeList();
                $nodeids = implode(',',  array_keys($nodeModel->getViewableNodeList()));
            }
            //unset($latestNodes[X], $latestNodes[Y]); // X, Y are the IDs of publicly viewable forums but I don't want to retrieve threads from them         
            $latestthreads = array();
            // Here I use XF's own $db connector (Zend_Db), but you can do it another away
            $db = XenForo_Application::getDb();
            $query = '
                SELECT xt.thread_id, xt.title, xt.reply_count, xt.post_date, xt.node_id, xn.title as forumtitle, xt.username
                    FROM xf_thread as xt
                    LEFT JOIN xf_node as xn on xt.node_id = xn.node_id
                    WHERE xt.node_id IN (' . $nodeids . ') 
                   AND xt.discussion_state = "visible"
                   AND xt.discussion_open = 1
                   AND xt.sticky = 0
               ORDER BY xt.post_date DESC
               LIMIT '.$limit.';           
            ';
            $threads = $db->query($query);
    
            while ($thread = $threads->fetch()) {   
                //error_log("Thread: ".print_r($thread,1));
               $latestthreads[$thread['thread_id']] = array(
                   'id' => $thread['thread_id'],
                   'title' => $thread['title'],
                   'node' => $thread['node_id'],
                   'username' => $thread['username'],
                   'forumtitle' => $thread['forumtitle'],
                   'forumurl' => XenForo_Link::buildPublicLink('canonical:forums',array('node_id' => $thread['node_id'], 'title' => $thread['forumtitle'])),
                   'url' => XenForo_Link::buildPublicLink('canonical:threads', $thread),
                   'replycount' => $thread['reply_count'],
                   'dateline' => $thread['post_date']
               );
            }
            return $latestthreads;       
    
    But it seems that for posts I could go another way, querying the model for latest updated threads and than I could employ PostModel to query for last post of each threaid retrieved from XenForo_Model_Thread. Is one of these ways inherently better than the other?

    Code:
           $threadModel = XenForo_Model::create('XenForo_Model_Thread');
            if ($nodeids == '') {
                $nodeids = $nodeModel->getViewableNodeList();
                //$nodeids = implode(',',  array_keys($nodeModel->getViewableNodeList()));
            }
            $latestposts = array();
            //error_log($nodeids);
            $threads = $threadModel->getThreads(
                array(
                    'node_id' => $nodeids,
                    'deleted' => false,
                    'moderated' => false,
                    'reply_count' =>array('>',0)
                ),
                array(
                    'limit' => $limit,
                    'order' => 'last_post_date',
                    'orderDirection' => 'desc',
                    'join' => 0
                )
            );
    
     
  2. LPH

    LPH Well-Known Member

    For threads, I use the model XenForo_Model_Threads:

    PHP:
            /** @var $forumModel XenForo_Model_Forum */
            
    $forumModel XenForo_Model::create('XenForo_Model_Forum');
            
    $visitor XenWord::getVisitor();
            
    $permissionCombinationId $visitor['permission_combination_id'];

            
    /** @var $threadsModel XenForo_Model_Thread */
            
    $threadsModel XenForo_Model::create('XenForo_Model_Thread')->getModelFromCache('XenForo_Model_Thread');
            
    $threads $threadsModel->getThreads(array('discussion_state' => 'visible''permissionCombinationId' => $permissionCombinationId), array('limit' => $instance['numberposts']*3'order' => 'thread_id''orderDirection' => 'desc') );

            
    ob_start();
            
    $i 0;
            foreach ( 
    $threads AS $thread ) {
                
    $visitor->setNodePermissions$thread['node_id'], $thread['node_permission_cache'] );

                if ( 
    $forumModel->canViewForum$thread ) ) {
                    if ( 
    $i == $instance['numberposts']  ) break;
                        echo(
                            
    "<div class='entry-meta'><a href='"
                            
    XenForo_Link::buildPublicLink'canonical:threads'$thread )
                            . 
    "'>"
                            
    XenForo_Helper_String::wholeWordTrim$thread['title'], 40 )
                            . 
    "</a></div>"
                        
    );
                        
    $i++;
                }
            }

            echo 
    '<span style="float:right"><a href="' XenWord::getBoardUrl() . '/find-new/posts">View more threads</a></span><br />';
     
  3. janslu

    janslu Member

    Thank you! You put on the right track, and ->getModelFromCache is a great addition! I came up with the following to retrieve latest posts:

    Code:
        public function latestPosts($nodeids,$limit=4) {
            $nodeModel = XenForo_Model::create('XenForo_Model_Node')->getModelFromCache('XenForo_Model_Node');
            $threadModel = XenForo_Model::create('XenForo_Model_Thread')->getModelFromCache('XenForo_Model_Thread');
            $postModel = XenForo_Model::create('XenForo_Model_Post')->getModelFromCache('XenForo_Model_Post');
            if ($nodeids == '') {
                $nodeids = array_keys($nodeModel->getViewableNodeList());
                //remove node 637
                $nodeids = array_diff($nodeids, array(637));
            }
            $latestposts = array();
            $threads = $threadModel->getThreads(
                array(
                    'node_id' => $nodeids,
                    'deleted' => false,
                    'moderated' => false,
                    'reply_count' =>array('>',0)
                ),
                array(
                    'limit' => $limit,
                    'order' => 'last_post_date',
                    'orderDirection' => 'desc',
                    'join' => XenForo_Model_Thread::FETCH_FORUM
                )
            );
            foreach ($threads as $thread) {
                $post = $postModel->getLastPostInThread(@$thread['thread_id']);
              
                //strip bbcode
                $message = XenForo_Helper_String::bbCodeStrip($post['message']);
                $message = preg_replace('/\[(attach|media|img|spoiler)\]/siU', '', $message);
                $message = preg_replace('/^[\t\s]*(\r?\n){2,}/m', '', $message);
    
                //output array
               $latestposts[$thread['thread_id']] = array(
                   'post_id' => $post['post_id'],
                   'thread_id' => $thread['thread_id'],
                   'title' => $thread['title'],
                   'message' => $message,
                   'node' => $thread['node_id'],
                   'username' => $post['username'],
                   'forumtitle' => $thread['node_title'],
                   'forumurl' => XenForo_Link::buildPublicLink('canonical:forums',array('node_id' => $thread['node_id'], 'title' => $thread['node_title'])),
                   'url' => XenForo_Link::buildPublicLink('canonical:posts', $post),
                   'replycount' => $thread['reply_count'],
                   'dateline' => $post['post_date']
               );          
            }
            return $latestposts;
        }
    
     
    LPH likes this.

Share This Page