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

[janslu]

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:

$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?

       $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
            )
        );

 

[LPH]

For threads, I use the model XenForo_Model_Threads:

        /** @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 />';

 

[janslu]

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

    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;
    }