5 min read

Search Magento products by product labels

Search Magento products by product labels

Last modified

Project Overview:

Task was to develop a Product Labels Magento extension. Where admin can create product labels by uploading an image, enter label title and attach them to any number of products available in store. The label should display on front-end along with product information.

The Challenge:

Customers can search products by their labels i.e if they put label name in search box then all products attached with that label should pull and existing search should also work as is that means if the search text matches any product attribute like name description of any search-able attribute those product should list in search results.

Problem faced:

As the labels are managed through a custom module and the label is not a product attribute, its not straight forward task to extend the Magento search to find products based on custom table.

Problem Resolution:

I was unable to find any related help of sample solution from any blog/forum. So I decided to read the Magento core and find how the search works and how above requirements can be handled in a sleek way that doesn’t violate Magento coding conventions and best practices.

Here is the solution I found.

Create a custom module and override the Magento catalog search model in config.xml.


Inside your custom model class implement the prepareResult() method as shown below.

 * Prepare results for query
 * @param Mage_CatalogSearch_Model_Fulltext $object
 * @param string $queryText
 * @param Mage_CatalogSearch_Model_Query $query
 * @return Mage_CatalogSearch_Model_Resource_Fulltext
public function prepareResult($object, $queryText, $query)

    $adapter = $this->_getWriteAdapter();
    if (!$query->getIsProcessed()) {
        $searchType = $object->getSearchType($query->getStoreId());

        $preparedTerms = Mage::getResourceHelper('catalogsearch')
                ->prepareTerms($queryText, $query->getMaxQueryWords());

        $bind = array();
        $like = array();
        $likeCond = '';
        if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_LIKE || $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE
        ) {
            $helper = Mage::getResourceHelper('core');
            $words = Mage::helper('core/string')->splitWords($queryText, true, $query->getMaxQueryWords());
            foreach ($words as $word) {
                $like[] = $helper->getCILike('s.data_index', $word, array('position' => 'any'));
            if ($like) {
                $likeCond = '(' . join(' OR ', $like) . ')';
        $mainTableAlias = 's';
        $fields = array(
            'query_id' => new Zend_Db_Expr($query->getId()),
        $select = $adapter->select()
                ->from(array($mainTableAlias => $this->getMainTable()), $fields)
                ->joinInner(array('e' => $this->getTable('catalog/product')), 'e.entity_id = s.product_id', array())
                ->where($mainTableAlias . '.store_id = ?', (int) $query->getStoreId());

        if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_FULLTEXT || $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE
        ) {
            $bind[':query'] = implode(' ', $preparedTerms[0]);
            $where = Mage::getResourceHelper('catalogsearch')
                    ->chooseFulltext($this->getMainTable(), $mainTableAlias, $select);

        if ($likeCond != '' && $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE) {
            $where .= ($where ? ' OR ' : '') . $likeCond;
        } elseif ($likeCond != '' && $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_LIKE) {
            $select->columns(array('relevance' => new Zend_Db_Expr(0)));
            $where = $likeCond;

        if ($where != '') {

        // Search products by their Labels and inject results in search results table
        $_collection = Mage::getResourceModel('Scriptbaker_productlabels/productlabel_collection');
        $_collection->addFieldToFilter('title', array('like' => '%' . $queryText . '%'));
        if (count($_collection) > 0) {
            foreach ($_collection->getItems() as $productlabel) {
                $products = Mage::getResourceModel('Scriptbaker_productlabels/productlabel_product_collection')
                foreach ($products as $product) {
                    $linkedResults = array(
                        'query_id' => new Zend_Db_Expr($query->getId()),
                        'product_id' => $product->getEntityId()
                    $adapter->insert($this->getTable('catalogsearch/result'), $linkedResults);

        $sql = $adapter->insertFromSelect($select, $this->getTable('catalogsearch/result'), array(), Varien_Db_Adapter_Interface::INSERT_ON_DUPLICATE);
        $adapter->query($sql, $bind);


    return $this;

As you can see in above code, first the product labels are fetched by the label text then product label collection is fetched. Which returns ids of all products having that label. Next we insert the product ids in ‘catalogsearch/result’ table against the search term.


Reading Magento core files is always a good idea to find your way.