如何以及在何处修改 Magento 搜索查询? [英] How and where to modify Magento search query?

问题描述

首先，我在SO中发现了类似的问题，但没有任何答案.所以，问题的第一部分有点重复.我想改进 Magento 中的搜索结果.这是我已经完成的:

First of all, I found similar questions in SO but there is not any answer for them. So, the first part of the question is a little bit duplicated. I want to improve search results in Magento. Here is what I've done already:

1. 当有多个词时，用 AND 而不是 OR 搜索.

1. Search with AND instead of OR when there are multiple words.

2. Ajax 搜索从任何地方开始搜索，而不仅仅是从字段的开头.

2. Ajax search starts searching from anywhere and not only from the beginning of the fields.

3. 修剪单词的最后一个 s 以防止使用复数搜索时出现空结果.

3. Trim the last s from the words to prevent empty results when searching with plurals.

4. 我将搜索类型从 Like 更改为 Fulltext 或 Combine 但结果并不好甚至是最糟糕的，所以我保持原样.现在是 Like，所以没有 relevance 排序.

4. I changed the search type from Like to Fulltext or Combine but the results were not better and even were worst, so I leave it as is. It's Like now, so there is no relevance ordering.

我想尝试的最后一件事是将此添加到搜索查询:

The last thing which I want to try is adding this to the search query:

SELECT ... other non-full-text-cols
MATCH (product_title) AGAINST ('lean body for her') AS rel1,
MATCH (content) AGAINST ('lean body for her') AS rel2
FROM table
WHERE MATCH (product_title,content) AGAINST ('lean body for her')
ORDER BY (rel1*1.5)+(rel2)

这是我的查询，但我不确定它是否有效，因为我无法测试它:

Here is my query but I'm not sure if it would work because I can't test it:

$this->_productCollection->addAttributeToSelect(
    array(
    'rel1' => new Zend_Db_Expr('MATCH (name) AGAINST ("'.$queryText.'")'),
    'rel2' => new Zend_Db_Expr('MATCH (short_description) AGAINST ("'.$queryText.'")')
    )
);

$this->_productCollection->getSelect()
    ->where('MATCH (name,short_description) AGAINST ("'.$queryText.'")')
    ->order('(rel1*1.5)+(rel2)');

主要思想是如果在产品标题中找到搜索查询，则为结果添加奖励权重.问题是我不知道在哪里修改查询.我根本找不到它在哪里.$this->_productCollection 不是正确的对象，我知道.我查看了所有 Collection.php 文件、资源模型、模型甚至查询日志，但没有运气.某些文件中只有很少的 1 或 2 行部分，但不是完整的查询.我是 Magento 的新手，但在查找此类内容时仍然遇到问题.那么，当我必须扩展查询时，我必须在哪里放置额外的东西?

The main idea is to add bonus weight to a result if the search query is found in the title of the product. The problem is that I don't know where to modify the query. I can't find where it is at all. $this->_productCollection is not the right object, I know it. I looked at all the Collection.php files, resource models, models and even the query log but no luck. There are just little 1 or 2 row parts in some files but not a full query. I'm new to Magento and still have problems with finding this type of stuff. So, where I have to place my additional stuff when I have to extend a query?

社区版 Magento，版本 1.6.1.0.

Community Edition Magento, version 1.6.1.0.

注意:我知道一些用于改进搜索结果的扩展程序比我的解决方案效果更好，但现在我必须这样做.这对我来说也是一次很好的体验.

Note: I know that some extension for improving search results will work much better than my solutions but for now I have to do it in that way. It would be a good experience for me, too.

所以，我想出了如何为订购添加我的自定义字段，但它
我不真实地认为.在 class Mage_CatalogSearch_Model_Layer extends Mage_Catalog_Model_Layer 的 prepareProductCollection 方法中，我向查询添加了两个连接并获取字段 rel1 和 rel2:

So, I figured out how to add my custom fields for the ordering but it's
untruly I think. In class Mage_CatalogSearch_Model_Layer extends Mage_Catalog_Model_Layer's prepareProductCollection method I added two joins to the query and get the fields rel1 and rel2:

$collection->getSelect()->joinLeft(
    array('cpev' => 'catalog_product_entity_varchar'),
    'cpev.entity_id = e.entity_id AND cpev.attribute_id = 96',
    array('rel1' => new Zend_Db_Expr('2.01*(LENGTH(cpev.value) - LENGTH(REPLACE(LCASE(cpev.value), LCASE("'.$queryText.'"), ""))) / LENGTH("'.$queryText.'")'))
);

$collection->getSelect()->joinLeft(
    array('cpet' => 'catalog_product_entity_text'),
    'cpet.entity_id = e.entity_id AND cpet.attribute_id = 506',
    array('rel2' => new Zend_Db_Expr('(LENGTH(cpet.value) - LENGTH(REPLACE(LCASE(cpet.value), LCASE("'.$queryText.'"), ""))) / LENGTH("'.$queryText.'")'))
);

我现在有这些字段，但正如你所看到的，我有硬编码的东西，比如 attribute_id = 96 等，这根本不好，而且每次都不起作用 - 我直接从数据库表.我这样写是因为我无法访问 name 和 short_description 字段，但它们在结果中.不知道为什么.所以，cpev.value 是 name 字段，cpet.value 是 short_description 字段.此外，我无法按这些字段对结果进行排序.我试过 $collection->addOrder('SUM(rel1+rel2)');, $collection->getSelect()->order(new Zend_Db_Expr('SUM(rel1)+rel2)').' DESC');、一些 addAttributeToFilter 的东西等等，但它不起作用.

I have these fields now but as you can see I have hard coded stuff like attribute_id = 96 etc. which is not good at all and it will not work everytime - I checked these ids directly from the database tables. I wrote it like this because I haven't access to name and short_description fields but they are in the result. Don't know why. So, cpev.value is name field and cpet.value is the short_description field. Moreover I can't order the results by these fields. I tried $collection->addOrder('SUM(rel1+rel2)');, $collection->getSelect()->order(new Zend_Db_Expr('SUM(rel1+rel2)').' DESC');, some addAttributeToFilter stuff etc. but it's not working.

编辑 2:我接受了 @james 的回答，但最终我们购买了一个扩展程序来改进搜索结果.

Edit 2: I accepted @james' answer but finally we bought an extension for improving the search results.

推荐答案

在 Mage_CatalogSearch_Model_Resource_Fulltext 中查看 prepareResult(1.7 CE 中的第 310 行)并查找以下内容:

In Mage_CatalogSearch_Model_Resource_Fulltext check out prepareResult (line 310 in 1.7 CE) and look for the following:

$select->columns(array('relevance'  => new Zend_Db_Expr(0)));

Magento 将所有搜索结果相关性设置为 0 (!)；在此处添加您想要的相关性(越高越好).您可以在 Zend_Db_Expr() 中创建自定义查询以生成更高的属性匹配相关性.

Magento sets all search result relevances as 0 (!); add the relevances you want (higher is better) here. You can create a custom query in Zend_Db_Expr() to generate higher relevances on attribute matches.

这篇关于如何以及在何处修改 Magento 搜索查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！