How and where to modify Magento search query?

How and where to modify Magento search query?

October 6, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How and where to modify Magento search query?

Improving Magento Search Results: A Comprehensive Guide

Hello developers! Today we’re going to dive deep into hacking your way around Magento’s search functionality. I’m assuming you’re as frustrated as I was with the default search engine - it’s like a blind man trying to find his glasses in a dark room.

What I’ve Tried So Far

Before I share my final solution, let me walk you through what I attempted:

  1. Changing the search logic: I made the search operate on an AND instead of an OR when multiple words are entered. This narrowed down the results significantly.
  2. Ajax search improvements: I tweaked the Ajax search to start scanning from any position in the field, not just the beginning.
  3. Plural handling: To prevent empty results when searching with plurals (e.g., “shirts” instead of “shirt”), I trimmed the last ‘s’ from the words.
  4. Search type experiments: I tried changing the search type to Fulltext or Combine, but it didn’t improve my results.

The Magic Formula

Now, here’s where things get interesting! I found this gem online:

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)

It suggests adding relevance scores for the search query found in both product_title and content. I tried porting it to Magento’s Zend_Db_Expr:

$this->getSelect()
    ->where('MATCH (name,short_description) AGAINST (?)')
    ->orderBy(new Zend_Db_Expr('(rel1*1.5)+(rel2)'), Varien_Data_Collection::SORT_ORDER_DESC);

Where to Put the Magic

Now comes the million-dollar question: Where do I put this?

After much searching in the CatalogSearchIndexController, Layer.php and various other files, I realized it’s not that straightforward. The search query is built up piece by piece across multiple methods.

But here’s a hacky solution:

My Hacky Solution

Find this line: $this->getSelect()->joinLeft($tableAlias, $condition);

Add these two joins right after it:

$this->getSelect()
    ->joinLeft(
        array('cpev' => 'catalog_product_entity_varchar'),
        'cpev.entity_id = e.entity_id AND cpev.attribute_id IN (96)',
        array('rel1' => new Zend_Db_Expr('2.01*(LENGTH(cpev.value) - LENGTH(REPLACE(LCASE(cpev.value), LCASE(?), ""))) / LENGTH(?)'))
    )
    ->joinLeft(
        array('cpet' => 'catalog_product_entity_text'),
        'cpet.entity_id = e.entity_id AND cpet.attribute_id IN (506)',
        array('rel2' => new Zend_Db_Expr('(LENGTH(cpet.value) - LENGTH(REPLACE(LCASE(cpet.value), LCASE(?), ""))) / LENGTH(?)'))
    );

This adds the relevance scores for name and short_description. The ? is a placeholder for your search query.

The Drawbacks

While this solution works, it’s not perfect:

  1. Hardcoded attribute IDs: I used hardcoded attribute IDs which isn’t ideal.
  2. Limited fields: It only scores on name and short_description.
  3. No relevance ordering: Even with the added scores, I couldn’t get Magento to order by them.

The Conclusion

Improving Magento’s search is a complex task that requires deep knowledge of its core components. While my solution provides some improvements, it’s far from perfect.

For serious e-commerce sites, consider investing in professional extensions like Amasty Improved Search or MageWorx Advanced Search. They provide advanced search functionality that’s impossible to replicate with custom code alone.

Remember, the best way to learn is by doing - even if your first attempts aren’t perfect. Keep experimenting and you’ll eventually crack the Magento search!

Stay tuned for more Magento hacks!

Catalog Database API Orders

Our network