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

[LPH]

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.

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

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

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]

It's encouraged to use the XenForo Models plus then you can do it all in one line

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

 

[LPH]

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.

 

[LPH]

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

        $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

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

 

[truonglv]

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

        $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

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

`discussion_state` should be in range values: 'visible', 'moderated', 'deleted' not `1`

I see in the database the 'visible' etc but the '1' was working too. Is there are difference?

 

[Bob]

I see in the database the 'visible' etc but the '1' was working too. Is there are difference?

Read this: http://dev.mysql.com/doc/refman/5.6/en/enum.html and it will become clear

 

[LPH]

Read this: http://dev.mysql.com/doc/refman/5.6/en/enum.html and it will become clear

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.

$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