Magento / PHP - 修改前端产品搜索框底层的MySQL查询

Question

Currently, the MySQL database queries that supply the results for the product search field on the front end seem to use "OR" linking criteria in the WHERE clause of the queries.

The reason I assume it is using "OR" is because if you search for something like "green and red plaid shirt", you will get every product with "red" (including "bored", "stored", etc), every product with "green", every product with "plaid", and every product with "shirt".

Now if I can just find out where in the code the queries are being constructed, I should be able to change that to "AND" and end up with queries like this:

SELECT `product_id` FROM `products` WHERE `search_index` LIKE '%red%' AND `search_index` LIKE '%green%' AND `search_index` LIKE '%plaid%' AND `search_index` LIKE '%shirt%';

I haven't been able to find any information by searching Google or Magento's forums. I've been poking around app/code/core/Mage/CatalogSearch/ but have not found the mother lode yet. I know that there is probably some Zend interface I should mess with but haven't found it yet.

Thanks in advance

Answer 1

UPDATE

The below answer does not seem to work for Magento 1.7+, since they've changed some of the search code. I'm working on a solution for that and will update later.

---

I'm going to answer my own question. Thanks, Anton S for the clues there but I located some key files myself and was able to implement the changes I wanted.

Here is the key file: app/code/core/Mage/CatalogSearch/Model/Mysql4/Fulltext.php

You would copy the core structure that leads to that file into the local structure, and copy the core file there as well, like so: app/code/local/Mage/CatalogSearch/Model/Mysql4/Fulltext.php

Then make all changes to the local file, leaving the core file alone.

Look for this bit of code around line 315, inside the function prepareResult($object, $queryText, $query):

foreach($words as $word) {
    $like[ ] = '`s`.`data_index` LIKE :likew' . $likeI;
    $bind[':likew' . $likeI] = '%' . $word . '%';
    $likeI ++;
}
if ($like) {
    $likeCond = '(' . join(' OR ', $like). ')';
}

That ' OR ' there is what was giving me thousands of useless results. For example, a search for "green and red plaid shirt" would end up showing me all things green, red, and/or plaid (including shirts, skirts, blimps, rabbits), as well as every single shirt in the store. What the user really wants to find is a product that contains ALL search terms. As noted above, you would also find results like "bored" and "stored" because they contain "red."

To solve most of the problem, you simply have to change that ' OR ' to an ' AND '. Also note that the change only applies to "LIKE" type searches, not "FULLTEXT" type. FULLTEXT doesn't work well in Magento because it excludes way too many results. The method outlined below is much better.

To make the changes:

1. save the file with the change above.
2. go into the admin, to System->Catalog->Catalog Search and make sure Search Type is "Like".
3. Save the configuration

4. In the admin, go to system->Index Management, and check the box next to Catalog Search Index and reindex it (or just reindex all). (OR from the command line in the magento root type:

   php shell/indexer.php --reindex catalogsearch_fulltext

)

If you also want to exclude words like "bored" when searching for "red", then you might want to implement a 2nd change in the same file.

There is another section of code inside that reads:

$bind[':likew' . $likeI] = '%' . $word . '%';

The % at the front means that "bored" is like %red%. But you can't just remove the 1st % to get the right effect because of the way the index is constructed. So instead you make these two changes:

1. change the above line of code to:

   $bind[':likew' . $likeI] = '% ' . $word . '%';

   Note the SPACE after the first % before the closing quote. This will now only find words that start with $word (e.g. red, redding, reddy, rediculous all match '% red%'), but you also have to ensure that all words will have spaces before them.

2. Near the top of the file, under class Mage_CatalogSearch_Model_Mysql4_Fulltext, around line 48 you should find this:

   protected $_separator = '|';

I just changed it to this:

protected $_separator = ' | ';

Putting spaces on both sides of the pipe. When you reindex, there will now be spaces before and after every word. A search for "kit" will still give you results for "kitchen", but at least you won't get results for "skit".

Finally, one last change I made was to ensure plural searches return the same results as singular searches, at least for plurals ending in 's'. I added a line of code where indicated:

foreach($words as $word) {
    $word = rtrim($word, 's'); //this line added
    $like[ ] = '`s`.`data_index` LIKE :likew' . $likeI;
    $bind[':likew' . $likeI] = '%' . $word . '%';
    $likeI ++;
}

It simply chops the 's' off the end of every word, so now "red and green plaid shirts" returns the same results as "reds ands greens plaids shirt".

My next project may be to make some more changes to the string parsing to get better results for multi-word searches. I'm looking at this file, fyi: app/code/core/Mage/Core/Helper/String.php

function splitWords

,which is used in the Fulltext.php file for string parsing.

NOTE: To make this change upgrade-safe, you would duplicate the folder structure past app/code/core inside app/code/local, like this: app/code/local/Mage/CatalogSearch/Model/Mysql4/Fulltext.php

Just copy the core file there, and make your changes there.