如何和在哪里修改Magento搜索查询？ [英] How and where to modify Magento search query?

问题描述

首先，我在SO中发现了类似的问题，但他们没有任何答案。所以，问题的第一部分是有点重复。我想提高在Magento的搜索结果。这是我已经做了：

1。使用 AND 或。

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

3。修改字词中的最后一个 s

4。我将搜索类型从更改为更改为 Fulltext 或 Combine 但结果不是更好，甚至是最差的，所以我保持原样。现在像，因此没有相关性排序。

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

  SELECT ...其他非全文本
 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）
  

这是我的查询，但我不知道是否会工作，因为我不能测试它：

  $ 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，仍然有问题，找到这种类型的东西。所以，当我必须扩展一个查询，我必须放置我的额外的东西？

社区版Magento，版本1.6.1.0。

注意：我知道一些扩展程序用于改进搜索结果将比我的解决方案工作得更好，但现在我必须这样做。这对我来说也是一个很好的经验。

编辑：

所以，我想出了如何添加我的自定义字段对于订单，但它是

untruly我想。在类Mage_CatalogSearch_Model_Layer扩展Mage_Catalog_Model_Layer 的 prepareProductCollection 方法我添加两个联接到查询，并获取字段 rel1 和 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 等，这是不好的，它不会工作每次 - 我直接从数据库表检查这些ids。我这样写，因为我没有访问 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 。

编辑2：
我接受了 @james 搜索结果。

解决方案

在 Mage_CatalogSearch_Model_Resource_Fulltext / code>（第13行第310行），并查找以下内容：

  $ select-> array（'relevance'=> new Zend_Db_Expr（0）））; 
  

Magento将所有搜索结果相关设置为0添加你想要的相关性（更高是更好）在这里。您可以在 Zend_Db_Expr（）中创建自定义查询，以在属性匹配中生成更高的相关性。

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. Search with AND instead of OR when there are multiple words.

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

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

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)');

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?

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.

Edit:

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.'")'))
);

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.

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

解决方案

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 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屋！