Hey, I've just been dealing with OC's Search engine way of work trying to customize it for my purposes and wanted to share my idea.
Basically speaking, Product Name and Model are essential elements of describing properties of my products. I needed to search those fields at once using multiple keywords randomly arranged, finding products containing all given keywords in Name and Model (optionally Description).
The reason I needed it to work this way was that the more keywords I used, the more results I got while using SQL's OR operator to combine Name and Model fields. I guess customers would (I would at last) use more keyword to narrow the search result.
Despite main changes being in the catalog/model/catalog/product.php file, I had to make some other minor changes in other files (eg. hiding "Search model/description" checkboxes, making it a default and only option). I'm at the testing stage right now, should you like me to contribute it as a mod, I'll try in spare time.
Last few words to explain what I wanted to achieve. Since Product Name, Model and Description are separate columns in separate tables, after trying Match() Against() (no luck), I thought it would be possible to search those three values at once by merging them together. Here comes SQL CONCAT function. A godsent, for me at last
BTW, I use vQMod to modify OC files.
Open file: catalog/model/catalog/product.php, find (2 occurences, ~182 and 252 line, about 10-line block of code):
Code: Select all
if (!$description) {
$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
} else {
$sql .= " AND (LCASE(pd.name) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%' OR LCASE(pd.description) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%'";
}
if (!$model) {
$sql .= ")";
} else {
$sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(strtolower($keyword)) . "%')";
}Code: Select all
$keyword = preg_replace("/^[^a-z0-9]+|[^a-z0-9]+$/", "", mb_strtolower($keyword, "UTF-8")); //trim special chars from ends, diacritics-safe strtolower - you can add your language specific diacritics to preg patterns
$keywords = explode(" ", $keyword); //explode multiple keywords
foreach ($keywords as $keyword) {
$sql .= " AND CONCAT(LCASE(pd.name), LCASE(pd.description), LCASE(p.model)) LIKE '%" . $this->db->escape(mb_strtolower($keyword, "UTF-8")) . "%'";
}How do you like it?
UPDATE:
For those of you who use vQMod and would like to try my modification I attach a vQMod file.
Changes it makes to storefront:
- disables "Advanced search" link,
- searches model and description by default,
- hides "Search model", "Search description" checkboxes.
Entering keywords followed by spaces seeks for all of them in Name, Model, Description altogether at once.
Entering quoted phrase seeks for it as an exact phrase in Name, Model, Description altogether at once.
, it makes it easy to always find the latest update in the first post.
Cheers.
I forgot that I had made changes to that file because of this thread:
