Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 3
Alter basic search to search for separate keywords instead of character string
 
raphael
Jr. Member
 
Total Posts:  10
Joined:  2008-05-06
Los Angeles
 

Right now the basic search function in Magento searches for a complete string. If multiple words are entered in the search, all the words have to appear in that order (as a complete string) in a product attribute field for the product to be returned in the results.

For the site I’m designing now, the client wants the basic search to function like keywords, not as character strings, because it’s very likely that the customer will input non-adjacent words as keywords into the basic search and thus not receive any results when, in fact, there are dozens of products the customer would want to see.

For example, there are a number of products with the phrase “copper air core inductor” in the title (and the description fields). They also have two custom attributes which label them as “copper” and as “inductor”. This function enables the advanced search to work very well for this. But in a basic search, it is very likely that visitors to this site would search for, say, “copper inductor”, which is not a string which appears in any attribute for those products. The basic search would then return 0 results.

I’ve been trying to alter the basic search to search for any fields in the product catalog with *any* of the individual terms, rather than all of them together as a character string, but I haven’t had any success yet. I’m no expert programmer, and not that familiar with Magento, but I’d like to show you what I’ve worked on and please tell me why it’s not working.

In my_site/app/code/core/Mage/CatalogSearch/Helper/Data.php, I’ve altered public function getQueryText() (starting on line 64) to read:

public function getQueryText()
    
{
        
if (is_null($this->_queryText)) {
            $this
->_queryText $this->_getRequest()->getParam($this->getQueryParamName());
            
// $this->_queryText = iconv_substr(trim($this->_queryText), 0, self::MAX_QUERY_LEN);
            
$results explode(' ',$this->_queryText);
            
$num_queries count($results);
            
$results_string "";
            for (
$i 0$i<$num_queries$i++) {
                
if ($i{
                $results_string 
.= "OR query_text like '" $results[$i] " %' ";
                
}
                
else {
                $results_string 
.= "query_text = '" $results[$i] " %'";
                
}
            }
            $this
->_queryText $results_string;

            
/**
             * Fix problem with
             * SQLSTATE[HY093]: Invalid parameter number: no parameters were bound
             */
            //$this->_queryText = str_replace('?', '', $this->_queryText);
        
}
        
return $this->_queryText;
    
}

The line that converts the inputted search into a string is commented out, and a number of new lines are added which explode the search term into an array of individual terms, then loops them through a for loop which separates them.

Secondly, in my_site/app/code/core/Mage/CatalogSearch/Model/Mysql4/Query/Collection.php I’ve altered public function setQueryFiler($query) (starting on line 33) to read:

public function setQueryFilter($query)
    
{
        $this
->getSelect()->reset(Zend_Db_Select::FROM)->distinct(true)
            ->
from(
                array(
'main_table'=>$this->getTable('catalogsearch/search_query')),
                array(
'query'=>"if(ifnull(synonim_for,'')<>'', synonim_for, query_text)"'num_results')
            )
            ->
where('num_results>0 and display_in_terms=1 and ($query)')
            ->
order('popularity desc');
        return 
$this;
    
}

The line starting with “->where” has been altered so that the $query variable is enclosed in parentheses and “search_query” and “%” are removed (they have been moved to Data.php).

This does not function, unfortunately. I think that perhaps some further function down the chain is reworking the SQL query and I can’t find it. I certainly have no idea what the Zend DB module is doing to these functions.

I will continue to hunt through the chain of functions which construct a SQL query for the basic search, but at the moment I am stumped and not familiar enough with Magento to know how to keep looking.

Could anybody offer any help with this challenge?

 
Magento Community Magento Community
Magento Community
Magento Community
 
westobou
Jr. Member
 
Total Posts:  5
Joined:  2008-05-26
 

I really need to know this as well.

 
Magento Community Magento Community
Magento Community
Magento Community
 
John Josef
Jr. Member
 
Total Posts:  10
Joined:  2008-07-01
 

Not sure but this might help.

public function getQueryText()
    
{
        
if (is_null($this->_queryText)) {
            $this
->_queryText $this->_getRequest()->getParam($this->getQueryParamName());
            
// $this->_queryText = iconv_substr(trim($this->_queryText), 0, self::MAX_QUERY_LEN);
            
$results explode(' ',$this->_queryText);
            
$results_string "";
            foreach (
$results as $i => $r{
                
if ($i{
                $results_string 
.= " OR query_text like '%" trim($r) . "%' ";
                
}
                
else {
                $results_string 
.= " query_text like '%" trim($r) . "%' ";
                
}
            }
            $this
->_queryText $results_string;

            
/**
             * Fix problem with
             * SQLSTATE[HY093]: Invalid parameter number: no parameters were bound
             */
            //$this->_queryText = str_replace('?', '', $this->_queryText);
        
}
        
return $this->_queryText;
    
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
RPGShop
Sr. Member
 
Total Posts:  143
Joined:  2008-06-16
 

I did this to my site with a simple little hack to the code (make a backup) found in:

app/code/core/Mage/CatalogSearch/Model/Mysql4/Search/Collection.php

Just edit the addSearchFilter function to replace all spaces with % in the search string.

/**
     * Add search query filter
     *
     * @param   string $query
     * @return  Mage_CatalogSearch_Model_Mysql4_Search_Collection
     */
    public function addSearchFilter($query)
    
{
        
// $this->_searchQuery = '%'.$query.'%';
        
$query eregi_replace(" ","%"$query);
        
$this->_searchQuery '%'.$query.'%';
        
$this->addFieldToFilter('entity_id', array('in'=>new Zend_Db_Expr($this->_getSearchEntityIdsSql($query))
));
        return 
$this;
    
}

Good luck
James
http://www.RPGShop.com

 
Magento Community Magento Community
Magento Community
Magento Community
 
westobou
Jr. Member
 
Total Posts:  5
Joined:  2008-05-26
 

Hey...I hope this works.  We’ve been checking daily to find a fix for search beyond a phrase issue.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ccoder
Jr. Member
 
Total Posts:  4
Joined:  2008-07-24
 

BUMP smile

Help!

 
Magento Community Magento Community
Magento Community
Magento Community
 
ccoder
Jr. Member
 
Total Posts:  4
Joined:  2008-07-24
 

My two cents…

From my tests RPGshop’s response enabled more searches to be found by mixing term order, but still turns up less result than a direct string e.g. “small chicken salad” may turn up more hits than “salad small”. This is a fairly straight forward search and could be done using a change to the query such as: (remove slashes, percent sign will not show up)

WHERE description like \&#xte;rm1\% or description like \&#xte;rm2\%

update to the search query, however, it seems to be built in over 3 files and I can not seem to find the line to edit… any ideas?

Results could easily get more fancy from here given a result set to modify the order on; according to order of the words and number of keyword hits.

 
Magento Community Magento Community
Magento Community
Magento Community
 
RPGShop
Sr. Member
 
Total Posts:  143
Joined:  2008-06-16
 

Since searching for a product is so integral and important to a store front, this really should have built in support! I’m not comfirtable hacking this any more then the quick fix I did… I hope they do something official though.

James

 
Magento Community Magento Community
Magento Community
Magento Community
 
johnjosephson
Jr. Member
 
Total Posts:  1
Joined:  2008-04-17
 

Excellent! At first blush, this seems to work perfectly. The client on the project was livid when he found out the search limitations. Thanks for the hack. Hopefully Varien will address this.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ccoder
Jr. Member
 
Total Posts:  4
Joined:  2008-07-24
 

Hello Varien, This is aggrivating. Please respond. Search is crucial to a site’s success.

 
Magento Community Magento Community
Magento Community
Magento Community
 
cstef
Jr. Member
 
Avatar
Total Posts:  10
Joined:  2008-06-17
 

I have added RPGShop’s hack, and it works great for splitting up the search terms into separate words (thank you!) However, the search terms still have to appear in the order that they are entered into the text box. 
Example:
Search for “ACME anvil” produces results.
Search for “anvil ACME” produces no results.

As someone else said, it seems like it would not be too hard to alter the SQL statement, but I can not find where the actual query is built and executed.

Please help!

 
Magento Community Magento Community
Magento Community
Magento Community
 
hyteckit
Sr. Member
 
Avatar
Total Posts:  166
Joined:  2008-04-01
Los Angeles, CA
 

The search function sucks.

 
Magento Community Magento Community
Magento Community
Magento Community
 
hyteckit
Sr. Member
 
Avatar
Total Posts:  166
Joined:  2008-04-01
Los Angeles, CA
 

It is downright awful.

 
Magento Community Magento Community
Magento Community
Magento Community
 
hyteckit
Sr. Member
 
Avatar
Total Posts:  166
Joined:  2008-04-01
Los Angeles, CA
 

It is practically useless.

 
Magento Community Magento Community
Magento Community
Magento Community
 
hyteckit
Sr. Member
 
Avatar
Total Posts:  166
Joined:  2008-04-01
Los Angeles, CA
 

Looks like it’s searching through the tables:

catalog_product_entity_text
catalog_product_entity_varchar

So basically the name of the product and description.  Maybe I should add searching tags.  That would be nice. Or maybe tags are already in those tables.

 
Magento Community Magento Community
Magento Community
Magento Community
 
hyteckit
Sr. Member
 
Avatar
Total Posts:  166
Joined:  2008-04-01
Los Angeles, CA
 

Cool, I just rewrote the search function to use RLIKE instead of LIKE.

It’s working well.  I was afraid RLIKE would be too slow.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 3