Programatically insert search term

Question

I want that all product name will show in catalog search suggestion list automatically, So I created a custom function in observer and call it on catalog_product_save_after event in config.xml

Here is my code for observer file : app/code/local/Mage/Adminhtml/Model/Observer.php

public function saveProductSearchTermForSuggestion($observer)
{
    $resource = Mage::getSingleton('core/resource');
    $connection = $resource->getConnection('core_write');
    $product = $observer->getProduct(); //Get saved product data
    $visibility = $product->getVisibility(); //Get saved product Visibility
    $status = $product->getStatus(); //Get saved product Status
    $productId = $product->getId(); //Get saved product Id
    $productStoreId = $product->getStoreId(); //Get saved product Id

    $productName = trim(strtolower($product->getName())); //Get saved product name
    $date  = date('Y-m-d H:i:s', time()); //Date
    $terms = array();
    $lastInsertId = '';

    $table = $resource->getTableName('catalogsearch_query');
    $queryTerms = $connection->fetchAll('SELECT Distinct query_text FROM '.$table);

    if (!empty($queryTerms)) {
        foreach ($queryTerms as $key => $val) {
            $terms[] = trim(strtolower($val['query_text'])); //array of all serach terms
        }
    }

    $searchVisibility = array(3,4);
    if (in_array($visibility,$searchVisibility) && $status == 1) { //Only for search type visible
        if (!empty($terms)) { //If database has values
            if (!in_array($productName, $terms)) {
                try {
                    //Insert into catalogsearch_query table for search term
                    $insQueryForTerm = "Insert into catalogsearch_query (query_id,query_text, num_results,popularity, redirect, synonym_for, store_id, display_in_terms, is_active, is_processed, updated_at) Values(NULL,'".ucwords($productName)."',1,1,NULL,NULL,1,1,1,0,'".$date."')";

                    $connection->query($insQueryForTerm);
                    $lastInsertId = $connection->lastInsertId(); //Last insertId

                    if ($lastInsertId != '') {
                        //Insert into catalogsearch_result to associate search term with productId
                        $insQueryForResult = "Insert into catalogsearch_result (query_id, product_id,relevance) Values(".$lastInsertId.",".$productId.",0.000)";
                        $connection->query($insQueryForResult);

                    }
                } catch (Mage_Core_Exception $e) {
                    Mage::logException($e);//If any exception save message in log
                }
            }
        } else //If no serach term in database {
            try {
                //Insert into catalogsearch_query table for search term
                $insQueryForTerm = "Insert into catalogsearch_query (query_id, query_text, num_results,popularity, redirect, synonym_for, store_id, display_in_terms, is_active, is_processed, updated_at) Values(NULL,'".ucwords($productName)."',1,1,NULL,NULL,1,1,1,0,'".$date."')";

                $connection->query($insQueryForTerm);
                $lastInsertId = $connection->lastInsertId(); //Last insertId
                if ($lastInsertId != '') {
                    //Insert into catalogsearch_result to associate search term with productId
                    $insQueryForResult = "Insert into catalogsearch_result (query_id, product_id,relevance) Values('".$lastInsertId."','".$productId."',0.0000)";
                    $connection->query($insQueryForResult);

                }
            } catch (Mage_Core_Exception $e) {
                Mage::logException($e); //If any exception save message in log
                //echo $e->getMessage(); 
            }
        }
    }
    //exit;
}

$insQueryForResult vaiable's query is not inserted into catalogsearch_result table.

In config.xml file: app/code/local/Mage/Adminhtml/etc/config.xml I use catalog_product_save_after event to call the above function.

I even try to make change in core's config.xml file still no data inserted in above table.

Please suggest what's going wrong here.

Answer 1

Instead of modifying core code you should add your own observer in a custom module:

Add this to

app/code/POOL/My/Module/etc/confi.xml

<events >
    <catalog_product_save_after >
        <observers>
            <add_search_term>
                <class>[module]/observer</class>
                <method>catalog_product_save_after</method>
            <add_search_term>
        </observers>
    </catalog_product_save_after >
</events>

You can try this observer code:

• it will add product name to search terms
• on product name change it will update search term record

app/code/POOL/My/Module/Model/Observer.php

public function catalog_product_save_after (Varien_Event_Observer $observer)
{
    $product = $observer->getProduct();

    // abort if saved in admin scope b/c search terms are store view specific
    if (!$product->getStoreId()) {
        return;
    }

    /**
    if ($product->getOrigData('name') === $product->getData('name')) {
        return;
    }
    */

    // had to add this to get store view value
    $attribute  = Mage::getSingleton('catalog/product')->getResource();
    $status     = $attribute->getAttributeRawValue($product->getId(), 'status', $product->getStoreId());
    $visibility = $attribute->getAttributeRawValue($product->getId(), 'visibility', $product->getStoreId());

    $searchVisibility = array(
        Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_SEARCH,
        Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH
    );
    if (!in_array($visibility, $searchVisibility) || $status == Mage_Catalog_Model_Product_Status::STATUS_DISABLED) {
        return;
    }

    $resource = Mage::getSingleton('core/resource');
    $connection = $resource->getConnection('core_write');

    $query = $connection->select()
        ->from($resource->getTableName('catalogsearch_query'), array('query_id'))
        ->where('query_text = ?', $product->getOrigData('name'))
        ->where('store_id = ?', $product->getStoreId());
    $rows = $connection->fetchCol($query, 'query_id');

    if (!count($rows)) {
        $rows = array(null);
    }

    foreach ($rows as $queryId) {
        try {
            $connection->insertOnDuplicate(
                $resource->getTableName('catalogsearch_query'),
                array(
                    'query_id' => $queryId,
                    'query_text' => $product->getName(),
                    'num_results' => 1,
                    'popularity' => 1,
                    'redirect' => NULL,
                    'synonym_for' => NULL,
                    'store_id' => $product->getStoreId(),
                    'display_in_terms' => 1,
                    'is_active' => 1,
                    'is_processed' => 0,
                    'updated_at'  => NULL
                ),
                array('query_text')
            );

            if (is_null($queryId)) {
                $queryId = $connection->lastInsertId();
            }

            $connection->insertOnDuplicate(
                $resource->getTableName('catalogsearch_result'),
                array(
                    'query_id' => $queryId,
                    'product_id' => $product->getId(),
                    'relevance' => 0.0000
                ),
                array()
            );
        } catch (Exception $e) {
            Mage::log($e->getMessage());
        }
    }
}