Xenforo sql search in titles

[cclaerhout]

I'm just wondering, is there a reason why the Xenforo Search system use the following SQL command (full-text search) when performing a 'Search titles only':

WHERE MATCH(`blabla`, `blabla`, `blabla`) AGAINST ('mysearchword' IN BOOLEAN MODE)

Instead of:

WHERE `title` LIKE 'mysearchword'

Or even :

WHERE `title` LIKE 'mysearchword%'

I'm asking this question because I've made a Chinese Words Index inside a forum. Most of the time a word is made of one or two characters. This word is inside the title of the post. So changing the sql command would be a solution to avoid the 'MySQL full text minimum word length' problem.

If you see no performance issue, I'm going to modify the search behaviour for one of my nodes.

 

[cclaerhout]

Ok, I did it and it's working. Here's my code if it can help someone

<?php
class Sedo_Mytest_Search_ChangeMinSearch extends XenForo_Search_SourceHandler_MySqlFt
{
 
    public function executeSearch($searchQuery, $titleOnly, array $processedConstraints, array $orderParts,
        $groupByDiscussionType, $maxResults, XenForo_Search_DataHandler_Abstract $typeHandler = null
    )
    {
       
        $options = XenForo_Application::get('options');
 
        if(isset($processedConstraints['node']['metadata'][1][0]))
        {
            //This part might be improved to implement the solution in multi forum search
            $nodeid = $processedConstraints['node']['metadata'][1][0];
       
 
            if(in_array($nodeid, $options->Afioc_Mytest_Forums) AND $titleOnly === TRUE AND preg_match('#\p{Han}+#u', $searchQuery))
            {
 
                $this->_minWordLength = '1';
           
                $queryParts = $this->tokenizeQuery($searchQuery);
 
                foreach($queryParts as $key => $part)
                {
                    if($key == 0)
                    {
                        $Where_Value = "$part[1]%";
                    }
                    else
                    {
                        $Or_Values[] = "$part[1]%";
                    }   
                }
 
                $variables['where'] = $Where_Value;
                $OR = '';
           
                if(isset($Or_Values) AND !empty($Or_Values))
                {
                    foreach ($Or_Values as $key => $value)
                    {
                        $OR .= "OR search_index.title LIKE ?\r\n";
                    }
               
                    $variables = array_merge($variables, $Or_Values);
                }
   
                if ($maxResults < 1)
                {
                    $maxResults = 100;
                }
                $maxResults = intval($maxResults);
       
 
                $db = $this->_getDb();
           
                return $db->fetchAll("
                    SELECT search_index.content_type, search_index.content_id
                    FROM xf_search_index AS search_index
                    WHERE search_index.title LIKE ?
                    $OR
                    AND search_index.metadata
                    REGEXP '_md_node_$nodeid'
                    ORDER BY search_index.item_date
                    LIMIT $maxResults
                ", $variables, Zend_Db::FETCH_NUM);
            }
        }
 
        $parent = parent::executeSearch($searchQuery, $titleOnly, $processedConstraints, $orderParts,$groupByDiscussionType, $maxResults, $typeHandler);
 
        return $parent;
    }
}

 

[cclaerhout]

The following code works in both title and message content:

<?php
class Sedo_Mytest_Search_ChangeMinSearch extends XenForo_Search_SourceHandler_MySqlFt
{

    public function executeSearch($searchQuery, $titleOnly, array $processedConstraints, array $orderParts,
        $groupByDiscussionType, $maxResults, XenForo_Search_DataHandler_Abstract $typeHandler = null
    )
    {
        
        $options = XenForo_Application::get('options');

        if(isset($processedConstraints['node']['metadata'][1][0]))
        {
            $nodeid = $processedConstraints['node']['metadata'][1][0];
        

            if(in_array($nodeid, $options->Afioc_Mytest_Forums) AND preg_match('#\p{Han}+#u', $searchQuery))
            {

                $this->_minWordLength = '1';
            
                $queryParts = $this->tokenizeQuery($searchQuery);

                foreach($queryParts as $key => $part)
                {
                    if($key == 0)
                    {
                        $Where_Value = "%$part[1]%"; 
                    }
                    else
                    {
                        $Or_Values[] = "%$part[1]%";
                    }    
                }

                $variables['where'] = $Where_Value;
                $OR = '';
            
                if(isset($Or_Values) AND !empty($Or_Values))
                {
                    foreach ($Or_Values as $key => $value)
                    {
                        $OR .= "OR search_index.title LIKE ?\r\n";

                        if($titleOnly === FALSE)
                        {
                            $OR .= "OR search_index.message LIKE ?\r\n";                        
                        }
                    }
                
                    $variables = array_merge($variables, $Or_Values);

                    if($titleOnly === FALSE)
                    {
                        $variables = array_merge($variables, $Or_Values);
                    }
                }
                elseif($titleOnly === FALSE)
                {
                    $OR .= "OR search_index.message LIKE ?\r\n";
                    $variables['or'] =  $Where_Value;
                }
    
                if ($maxResults < 1)
                {
                    $maxResults = 100;
                }
                $maxResults = intval($maxResults);
        

                $db = $this->_getDb();

                return $db->fetchAll("
                    SELECT search_index.content_type, search_index.content_id
                    FROM xf_search_index AS search_index
                    WHERE search_index.title LIKE ?
                    $OR
                    AND search_index.metadata
                    REGEXP '_md_node_$nodeid'
                    ORDER BY search_index.item_date
                    LIMIT $maxResults
                ", $variables, Zend_Db::FETCH_NUM);
            }
        }

        $parent = parent::executeSearch($searchQuery, $titleOnly, $processedConstraints, $orderParts,$groupByDiscussionType, $maxResults, $typeHandler);

        return $parent;
    }
}

 

[cclaerhout]

I guess my answer about performance is here. ^^ I'm wondering now how chinese forums do to avoid the sql min word length problem with sql full search...