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 2 of 3
Alter basic search to search for separate keywords instead of character string
 
cstef
Jr. Member
 
Avatar
Total Posts:  10
Joined:  2008-06-17
 

I modified the code the way hyteckit hinted at, (code?) but instead of narrowing searches by adding keywords, it returns more results.
Example:
Search for “ACME anvil” produces result with either of those words, meaning anvil without acme, and vice versa.
If I add “ACME anvil black” instead of narrowing it down to ACME brand anvils that are black, anything that has ACME, anvil, or black is returned.  Yargh!

I modified it by using REGEXP, and enclosing all of the keywords in the word delimiter:
[[:<:]]acme[[:>:]]

if there are more than 1 keyword, it creates the alternates delimiter “|”:
[[:<:]]acme|anvil|black[[:>:]]

This is all done in the addSearchFilter function.  In the queries, I changed “LIKE” to “REGEXP”

public function addSearchFilter($query)
        
{
            $query_array 
explode(' '$query);
            
            
$query_regex '';
            
            
$num_queries count($query_array);
            
$counter 1;
            
           
            foreach(
$query_array as $key => $value){
              $query_regex 
.= $value."|";
            
}
            
            $this
->_searchQuery "'[[:<:]]".$query_regex."[[:>:]]'";

                                 
            
$this->addFieldToFilter('entity_id', array('in'=>new Zend_Db_Expr($this->_getSearchEntityIdsSql($query))
        )); 
  
            return 
$this;
        
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
cstef
Jr. Member
 
Avatar
Total Posts:  10
Joined:  2008-06-17
 

UPDATE:

I have tried another way, modifying the RLIKE term to read:
[[:<:]]word1[[:>:]].*[[:<:]]word2[[:>:]]

This brings me back to square 1, where the terms must be typed in the correct order. 
word1 word2 = results
word2 word1 = no results.

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

Don’t bother modifying the function addSearchFilter()

Modify the function _getSearchEntityIdsSql() instead.

 
Magento Community Magento Community
Magento Community
Magento Community
 
joyously
Guru
 
Total Posts:  447
Joined:  2008-08-21
 
hyteckit - 23 August 2008 05:25 PM

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.

OK, I’ll bite. Where did you change it?
I tried changing LIKE to RLIKE in 3 places in app/code/core/Mage/CatalogSearch/Model/Mysql4/Search/Collection.php and my search wouldn’t find anything.

Oh yeah, I guess I have to change the wildcards too, huh? Are they all in that one file?

 
Magento Community Magento Community
Magento Community
Magento Community
 
hyteckit
Sr. Member
 
Avatar
Total Posts:  166
Joined:  2008-04-01
Los Angeles, CA
 
joyously - 25 August 2008 12:30 PM

hyteckit - 23 August 2008 05:25 PM
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.

OK, I’ll bite. Where did you change it?
I tried changing LIKE to RLIKE in 3 places in app/code/core/Mage/CatalogSearch/Model/Mysql4/Search/Collection.php and my search wouldn’t find anything.

Oh yeah, I guess I have to change the wildcards too, huh? Are they all in that one file?

Check out cstef post on how to use RLIKE/REGEXP.

 
Magento Community Magento Community
Magento Community
Magento Community
 
joyously
Guru
 
Total Posts:  447
Joined:  2008-08-21
 

OK fine, don’t tell me. But I don’t like the posted solution because it assumes that everything entered is a complete word.

I’m very familiar with regular expressions. They are very order dependent, as are SQL queries.  What you might have to do if you want your search to work like Google’s is to build your query something like this:

field RLIKE (search1)|(search1.*search2)|(search2.*search1)

Of course you’d have to make it even longer for more terms.

I don’t think that is a very good solution. The default search assumes an OR and you want an AND. Why not just change that while still separating the words as so: (I left the percent signs out)

field LIKE search1 AND field LIKE search2

What I did on a totally different site was concatenate my fields so I had a shorter query.
But I wasn’t doing the word separation with ANDs.

CONCAT_WS('~',field1,field2,field3,field4LIKE search

Oh, and I might mention that the MySQL manual has this:

REGEXP and RLIKE use the current character set when deciding the type of a character. The default is latin1 (cp1252 West European).
Warning

The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

It might be a concern if you are using another language.
 
Magento Community Magento Community
Magento Community
Magento Community
 
hyteckit
Sr. Member
 
Avatar
Total Posts:  166
Joined:  2008-04-01
Los Angeles, CA
 

joyously, if you wanted an answer, trying asking nicely and don’t be so cocky about being very familiar with regular expressions and SQL queries.  If you are very familiar with regular expressions and MySQL queries, you wouldn’t be asking me those questions in the first place.

Some basic MySQL and the RLIKE function.

If you want to match any of the search terms, you’ll use ‘OR’.

(field RLIKE '[[:<:]]term1[[:>:]]' OR field RLIKE '[[:<:]]term2[[:>:]]')

If you want to match all the search terms, you’ll use ‘AND’.

field RLIKE '[[:<:]]term1[[:>:]]' AND field RLIKE '[[:<:]]term2[[:>:]]'

So if I search for ‘iPhone Case’, I’ll split up the search terms into ‘iPhone’ and ‘Case’.  My SQL query would be:

field RLIKE '[[:<:]]iPhone[[:>:]]' AND field RLIKE '[[:<:]]Case[[:>:]]'

It wouldn’t matter if the search query is ‘iPhone Case’ or ‘Case iPhone’.

So the quickest and easiest solution is to modify the function _getSearchEntityIdsSql().  Split up the search terms and generate a SQL query where each term is check against the table field using the RLIKE function.  It should look something like:

field RLIKE '[[:<:]]iPhone[[:>:]]' AND field RLIKE '[[:<:]]Case[[:>:]]' AND field RLIKE '[[:<:]]Hot[[:>:]]' AND field RLIKE '[[:<:]]Pink[[:>:]]'

Example:

Searching for ‘iPhone Case Hot Pink’ turn up 0 results with the built-in Magento search function, because I don’t have any products with the exact phrase:

http://www.geekgears.com/catalogsearch/result/?q=iphone+case+hot+pink

With the modify search function, the search return 11 results:

http://www.geekgears.com/catalogsearch/enhanced/?q=iphone+case+hot+pink

If wouldn’t matter what the order of the search term is.

iPhone Hot Pink Case

Hot Pink iPhone Case

Case iPhone Hot Pink

 
Magento Community Magento Community
Magento Community
Magento Community
 
ylibrach
Sr. Member
 
Total Posts:  78
Joined:  2008-07-06
 

hyteckit, thank you for submitting your solution, it’s very smart and a great help. However, maybe for the future, you could tone down your remarks a little bit. We’re all here to help each other, after all…

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

hyteckit, is there any way you could post the function as you have it?  I tried to modify the way you show, but it is messing up somewhere, I don’t think I have it exactly right.  It would be greatly appreciated, the way your site searches is exactly how I need ours to.

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

Hey, I’m here to help you guys, not do the work for you guys.  If some of you can’t even ask nicely and pretend that they know it all, why do I even bother spending my time providing help?  I might as well spend my time working on my websites, getting paid to do consulting work, or getting some sleep.

cstef has the right attitude, because he is an active participant who tries to find the right solution with the tips I’ve provided, and is not just a passive participant waiting for others to do the work for them.  Those are the people I like to help.

I don’t like to help those who are passive participants who just whine, complain, and moan about no one is helping them and say how much they know about regular expressions and SQL queries, when the solution is just simple MYSQL using RLIKE.

There is no fancy trickery or crazy hack.  The solution is just simple MYSQL using RLIKE.

protected function _getSearchEntityIdsSql($query)
{
....
....

      
// hyteckit's addition

        
foreach($search_terms as $term){
            $parts[] 
"IFNULL(t2.value, t1.value) RLIKE '$term'";
        
}
        $parts 
implode(' AND '$parts);    

....
....
        foreach (
$tables as $table => $attributeIds{
            $param 
$table.'_search_query';
            
$selects[] $this->getConnection()->select()
                ->
from(array('t1' => $table), 'entity_id')
                ->
joinLeft(
                    array(
't2' => $table),
                    
$this->getConnection()->quoteInto('t1.entity_id = t2.entity_id AND t1.attribute_id = t2.attribute_id AND t2.store_id=?'$this->getStoreId()),
                    array()
                )
                ->
where('t1.attribute_id IN (?)'$attributeIds)
                ->
where('t1.store_id = ?'0)
                ->
where($parts);  // all the RLIKE parts added here

.....
.....

I might post my modify search in a week or two when I have the time.  My version involves creating 6 new classes, and not just modifying that one function, although modifying that one function would work just as well.

Anyway, good luck.  I’m going to waste less of my time here.

 
Magento Community Magento Community
Magento Community
Magento Community
 
joyously
Guru
 
Total Posts:  447
Joined:  2008-08-21
 
hyteckit - 26 August 2008 06:06 AM

I’m going to waste less of my time here.

Oh goody, then we won’t have so many lectures.

Like I said in my earlier post, there’s no reason to change to regular expressions (using RLIKE or REGEXP) and good reasons not to change.

Using the RLIKE with the word delimiters on the search terms assumes that what is entered is a word. But it might not be. I know I’ve searched for the last part of a long model number before, haven’t you?

If all you want is for the search terms to be able to match in any order, just change the OR to an AND in the code. (just like I said in my post)

 
Magento Community Magento Community
Magento Community
Magento Community
 
hyteckit
Sr. Member
 
Avatar
Total Posts:  166
Joined:  2008-04-01
Los Angeles, CA
 
joyously - 26 August 2008 06:25 PM

hyteckit - 26 August 2008 06:06 AM
I’m going to waste less of my time here.
Oh goody, then we won’t have so many lectures.

Like I said in my earlier post, there’s no reason to change to regular expressions (using RLIKE or REGEXP) and good reasons not to change.

Using the RLIKE with the word delimiters on the search terms assumes that what is entered is a word. But it might not be. I know I’ve searched for the last part of a long model number before, haven’t you?

If all you want is for the search terms to be able to match in any order, just change the OR to an AND in the code. (just like I said in my post)

Well, now that you know what function to modify, why don’t you share with us your solution for the benefit of the community, since you are the resident regex and SQL expert.

Hey, I’m no REGEX or MySQL expert.  Feel free to improve upon the sample code I’ve provided. Or maybe you just like to whine and moan, with no substance or anything to show for?

You want me to write a search engine like Google?  That’s funny.  LOL

 
Magento Community Magento Community
Magento Community
Magento Community
 
SimpleHelixcom
Enthusiast
 
Avatar
Total Posts:  906
Joined:  2007-08-31
Huntsville, AL
 

I doubt Google uses something like sql, it’ll be just too slow and return dumb results.
They use something more in the likes of their proprietary query engine, something like lucene (which apparently IS supported by Zend Framework, perhaps someone might want to take a shot at it?).

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

Just letting you guys know the quick hack Enhanced Search module is available on Magento Connect for those who are interested.

http://www.magentocommerce.com/extension/492/enhanced-search

I might come out with a Pro version that has more “Google-like” functions.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ylibrach
Sr. Member
 
Total Posts:  78
Joined:  2008-07-06
 

Thanks for posting that. Very useful. Have you looked into the issue with RLIKE that someone else alluded to, where if you use a number then the results wont be good?

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