title: How and where to modify Magento search query?
body: <p>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:</p>
  <p><strong>1.</strong> Search with <code>AND</code> instead of <code>OR</code> when there are multiple words.</p>
  <p><strong>2.</strong> Ajax search starts searching from anywhere and not only from the beginning of the fields.</p>
  <p><strong>3.</strong> Trim the last <code>s</code> from the words to prevent empty results when searching with plurals.</p>
  <p><strong>4.</strong> I changed the search type from <code>Like</code> to <code>Fulltext</code> or <code>Combine</code> but the results were not better and even were worst, so I leave it as is. It's <code>Like</code> now, so there is no <code>relevance</code> ordering.</p>
  <p>The last thing which I want to try is <a href="http://www.rocketweb.com/blog/improving-magento-search" rel="noreferrer">adding this to the search query</a>:</p>
  <pre><code>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)
  </code></pre>
  <p>Here is my query but I'm not sure if it would work because I can't test it:</p>
  <pre><code>$this-&gt;_productCollection-&gt;addAttributeToSelect(
      array(
      'rel1' =&gt; new Zend_Db_Expr('MATCH (name) AGAINST (&quot;'.$queryText.'&quot;)'),
      'rel2' =&gt; new Zend_Db_Expr('MATCH (short_description) AGAINST (&quot;'.$queryText.'&quot;)')
      )
  );
  
  $this-&gt;_productCollection-&gt;getSelect()
      -&gt;where('MATCH (name,short_description) AGAINST (&quot;'.$queryText.'&quot;)')
      -&gt;order('(rel1*1.5)+(rel2)');
  </code></pre>
  <p>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. <code>$this-&gt;_productCollection</code> is not the right object, I know it. I looked at all the <code>Collection.php</code> 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?</p>
  <p>Community Edition Magento, version 1.6.1.0.</p>
  <p>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.</p>
  <p><strong>Edit:</strong></p>
  <p>So, I figured out how to add my custom fields for the ordering but it's<br />
  untruly I think. In <code>class Mage_CatalogSearch_Model_Layer extends Mage_Catalog_Model_Layer</code>'s <code>prepareProductCollection</code> method I added two joins to the query and get the fields <code>rel1</code> and <code>rel2</code>:</p>
  <pre><code>$collection-&gt;getSelect()-&gt;joinLeft(
      array('cpev' =&gt; 'catalog_product_entity_varchar'),
      'cpev.entity_id = e.entity_id AND cpev.attribute_id = 96',
      array('rel1' =&gt; new Zend_Db_Expr('2.01*(LENGTH(cpev.value) - LENGTH(REPLACE(LCASE(cpev.value), LCASE(&quot;'.$queryText.'&quot;), &quot;&quot;))) / LENGTH(&quot;'.$queryText.'&quot;)'))
  );
  
  $collection-&gt;getSelect()-&gt;joinLeft(
      array('cpet' =&gt; 'catalog_product_entity_text'),
      'cpet.entity_id = e.entity_id AND cpet.attribute_id = 506',
      array('rel2' =&gt; new Zend_Db_Expr('(LENGTH(cpet.value) - LENGTH(REPLACE(LCASE(cpet.value), LCASE(&quot;'.$queryText.'&quot;), &quot;&quot;))) / LENGTH(&quot;'.$queryText.'&quot;)'))
  );
  </code></pre>
  <p>I have these fields now but as you can see I have hard coded stuff like <code>attribute_id = 96</code> 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 <code>name</code> and <code>short_description</code> fields but they are in the result. Don't know why. So, <code>cpev.value</code> is <code>name</code> field and <code>cpet.value</code> is the <code>short_description</code> field. Moreover I can't order the results by these fields. I tried <code>$collection-&gt;addOrder('SUM(rel1+rel2)');</code>, <code>$collection-&gt;getSelect()-&gt;order(new Zend_Db_Expr('SUM(rel1+rel2)').' DESC');</code>, some <code>addAttributeToFilter</code> stuff etc. but it's not working.</p>
  <p><strong>Edit 2:</strong>
  I accepted <em>@james</em>' answer but finally we bought an extension for improving the search results.</p>