Please help clean up working code to pull threads based on forum id

LPH

Well-known member
After almost a week, I have working code to pull a forum id from a select that is used in a query to list threads for that forum.

The code works but is wild that I must wrap quotes around $forum_number at one part to get the query to run properly.

PHP:
        $forum_number =  $instance['show_forum_number'];

        $forum_number = " \"$forum_number\" ";

        $select_thread_qry = '
                    SELECT thread_id, title, node_id, discussion_state FROM `xf_thread`
                    WHERE discussion_state = 1 AND node_id = ' .  $forum_number  . '
                    ORDER BY `thread_id` DESC
                    LIMIT 50
                    ';

        $select_threads = XenForo_Application::get( 'db' )->fetchAll( $select_thread_qry );

Do you have any suggestions?
 

Insy

Well-known member
PHP:
$db = XenForo_Application::get('db');

$forum_number = $instance['show_forum_number'];

$select_threads = $db->fetchAll('
   SELECT thread_id, title, node_id, discussion_state
   FROM `xf_thread`
   WHERE discussion_state = 1 AND node_id = ' . $db->quote($forum_number) . '
   ORDER BY `thread_id` DESC
   LIMIT 50
';

I've not tested this code
 

LPH

Well-known member
Ah. I learned something new - PDO::quote !

Yes. Your code almost works. It is missing the closing parenthesis in the fetchAll but ... once that is put in then it runs the query properly.
 

Daniel Hood

Well-known member
It's encouraged to use the XenForo Models plus then you can do it all in one line ;)

PHP:
$select_threads = XenForo_Model::create('XenForo_Model_Thread')->getThreads(array('node_id' => $instance['show_forum_number'], 'discussion_state' => 1), array('limit' => 50));
 
  • Like
Reactions: LPH

LPH

Well-known member
It's encouraged to use the XenForo Models plus then you can do it all in one line ;)

Thank you ! This is exactly what I must learn !

Version 3 of the bridge is focused on using more of the XenForo code and methods but it's a huge step moving from procedural and building the queries to learning the XF way. But it is exciting to learn better ways to integrate WP and XF.

(y)
 

LPH

Well-known member
Wow ! Two lines replaced all of that wild stuff. Best of all, the threads are returned much faster (no actual benchmarks ) !

(y)

PHP:
        $feature_node_qry = '
                    SELECT node_id, title, display_in_list, node_type_id FROM `xf_node`
                    WHERE display_in_list = 1 AND node_type_id = "Forum"
                    ORDER BY `title` ASC
                    ';

        $feature_nodes = XenForo_Application::get( 'db' )->fetchAll( $feature_node_qry );

        $db = XenForo_Application::get('db');

        $forum_number = $instance['show_forum_number'];

        $select_threads = $db->fetchAll('
               SELECT thread_id, title, node_id, discussion_state
               FROM `xf_thread`
               WHERE discussion_state = 1 AND node_id = ' . $db->quote($forum_number) . '
               ORDER BY `thread_id` DESC
               LIMIT 50
            ');

Replaced by

PHP:
$feature_nodes = XenForo_Model::create('XenForo_Model_Forum')->getForums(array('display_in_list' => 1 ) );

$select_threads = XenForo_Model::create('XenForo_Model_Thread')->getThreads(array('node_id' => $instance['show_forum_number']   , 'discussion_state' => 1), array('limit' => 50));
 
Last edited:

truonglv

Well-known member
Wow ! Two lines replaced all of that wild stuff. Best of all, the threads are returned much faster (no actual benchmarks ) !

(y)

PHP:
        $feature_node_qry = '
                    SELECT node_id, title, display_in_list, node_type_id FROM `xf_node`
                    WHERE display_in_list = 1 AND node_type_id = "Forum"
                    ORDER BY `title` ASC
                    ';

        $feature_nodes = XenForo_Application::get( 'db' )->fetchAll( $feature_node_qry );

        $db = XenForo_Application::get('db');

        $forum_number = $instance['show_forum_number'];

        $select_threads = $db->fetchAll('
               SELECT thread_id, title, node_id, discussion_state
               FROM `xf_thread`
               WHERE discussion_state = 1 AND node_id = ' . $db->quote($forum_number) . '
               ORDER BY `thread_id` DESC
               LIMIT 50
            ');

Replaced by

PHP:
$feature_nodes = XenForo_Model::create('XenForo_Model_Forum')->getForums(array('display_in_list' => 1 ) );

$select_threads = XenForo_Model::create('XenForo_Model_Thread')->getThreads(array('node_id' => $instance['show_forum_number']   , 'discussion_state' => 1), array('limit' => 50));
`discussion_state` should be in range values: 'visible', 'moderated', 'deleted' not `1`
 

LPH

Well-known member

Thank you Bob. Never do an ORDER BY using the index for enumeration sorting or there might be unexpected results. Got it.

I also see that the $fetchOptions were wrong for orderDirection. Here is the corrected code.

PHP:
$select_threads = XenForo_Model::create('XenForo_Model_Thread')->getThreads(array('node_id' => $instance['show_forum_number'], 'discussion_state' => 'visible'), array('limit' => 50, 'order' => 'thread_id', 'orderDirection' => 'desc') );

This seems to be working in the widget.

One line down ... thousands to go :D
 
Top