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

Xenforo sql search in titles

Discussion in 'XenForo Development Discussions' started by cclaerhout, Jul 20, 2012.

  1. cclaerhout

    cclaerhout Well-Known Member

    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':
    Code:
    WHERE MATCH(`blabla`, `blabla`, `blabla`) AGAINST ('mysearchword' IN BOOLEAN MODE)
    Instead of:
    Code:
    WHERE `title` LIKE 'mysearchword'
    Or even :
    Code:
    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.
     
  2. cclaerhout

    cclaerhout Well-Known Member

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

    PHP:
    <?php
    class Sedo_Mytest_Search_ChangeMinSearch extends XenForo_Search_SourceHandler_MySqlFt
    {
     
        public function 
    executeSearch($searchQuery$titleOnly, array $processedConstraints, array $orderParts,
            
    $groupByDiscussionType$maxResultsXenForo_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
                    "
    $variablesZend_Db::FETCH_NUM);
                }
            }
     
            
    $parent parent::executeSearch($searchQuery$titleOnly$processedConstraints$orderParts,$groupByDiscussionType$maxResults$typeHandler);
     
            return 
    $parent;
        }
    }
     
  3. cclaerhout

    cclaerhout Well-Known Member

    The following code works in both title and message content:
    PHP:
    <?php
    class Sedo_Mytest_Search_ChangeMinSearch extends XenForo_Search_SourceHandler_MySqlFt
    {

        public function 
    executeSearch($searchQuery$titleOnly, array $processedConstraints, array $orderParts,
            
    $groupByDiscussionType$maxResultsXenForo_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
                    "
    $variablesZend_Db::FETCH_NUM);
                }
            }

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

            return 
    $parent;
        }
    }
     
  4. cclaerhout

    cclaerhout Well-Known Member

    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...
     

Share This Page