Parent product in result on searching child sku

Question

I want to have functionality if a user searches for the child SKU, then parent products should appear in the result.
It is happening in my store but for few products not for all.

Example : I have a parent product A and its child are A1 and A2, if I am searching A1 or A2 then A is appearing in the search result.
I have one more parent product B and its child are B1 and B2, now when I am searching for B1 or B2, then B is not appearing in the search result.
Configuration for all the child products is nearly same.
Like all the child products are not visible individually and all parent product's visibility is catalog, search.

My first question is why this is working for only few products and not for all.
Secondly, how can I achieve this for all products.

Any help would be appreciated.

Answer 1

By default, the child product's SKU doesn't work in Catalog Search. You will not get the parent product in search result when you search by child product's SKU.

One simple solution to this can be:

• Create a new textField attribute named children_sku, for example.
• Make it searchable, i.e. Use in Quick Search = Yes
• Assign this newly created attribute to your attribute set.
• Now, while editing configurable product, add comma separated child products SKU in the children_sku attribute text field.

Answer 2

In my case it would be impossible/hassle when the website already has hundreds of product. My solution might be useful to someone.

I simply appended all the child SKUs in catalogsearch_fulltext table.

First change the search configuration for better results:

System > Catalog > Catalog Search > Search Type to "Combine (Like & Fulltext)

FYI, It is okay to keep the default config, ie, Search Type = "Like"

add event observer for catalogsearch_reset_search_result Here is how to create event observer https://goo.gl/eqwkyk

add this code in event observer class method

/**
 * Append these $childSKUs to fulltext 
 * db table catalogsearch_fulltext.data_index 
 * field with '|' seperated
 */
$fulltextCollection = Mage::getModel('catalogsearch/fulltext')->getCollection()
    ->addFieldToFilter('type_id', array('neq' => 'simple'));
$fulltextCollection->load();

$resource = Mage::getSingleton('core/resource');
$writeConnection = $resource->getConnection('core_write');
$tableName = $resource->getTableName('catalogsearch/fulltext');

foreach($fulltextCollection as $fulltext){

    $childSKUs = array();
    $fulltext = Mage::getModel('catalogsearch/fulltext')->load($fulltext->getId(), 'product_id');
    $p = Mage::getModel('catalog/product')->load($fulltext->getId());

    if( $p->getTypeId() == 'configurable'){

        $childrenIds = $p->getTypeInstance()->getUsedProductIds();
        $childrenSKUs = Mage::getResourceModel('catalog/product')->getProductsSku($childrenIds);
    }

    if(isset($childrenSKUs)) foreach($childrenSKUs as $c){
        $childSKUs[] = $c['sku'];
    }

    $dataIndex = $fulltext->getDataIndex();

    if(count($childSKUs)) $dataIndex .= '|' . implode('|', $childSKUs);

    /**
     * Have to use update query instead of model
     * fulltext resource uses product_id as key field
     * but fulltext_id is the primary key, so while 
     * updating through model it gives primary key duplicate error
     */
    $writeConnection->query("UPDATE $tableName SET data_index = :data_index WHERE product_id = :product_id", array('product_id' => $fulltext->getId(), 'data_index' => Mage::helper('core')->escapeHtml($dataIndex)));
}

Answer 3

I investigated and dig into the code. Below is the brief of my findings

When we are creating the search indexes by performing catalog search re-indexation, Magento update the table catalogsearch_fulltext with the product id and data_index. data_index contains the values of all the searchable attributes of the product itself and the associated products like brand, name, etc but it contains the sku only of the product and not of the associated product.

So, when a user is searching with a particular text, then magento tries to match this text with the values present in the data_index. If it gets any matching text then it shows that particular product in the result set.

Example values of data_index : AB2180|AB2180 Polished/Brushed|AB2180 Polished Chrome|AB2180 Brushed Nickel|||||1 Brief of above values : AB2180 : product sku AB2180 Polished/Brushed : product name AB2180 Polished Chrome|AB2180 Brushed Nickel : associated product names

Below are the table and files that helped me to understand

Database Table : catalogsearch_fulltext

CodeFiles or classes :

 - Mage_CatalogSearch_Model_Resource_Fulltext
 - Mage_CatalogSearch_Model_Resource_Fulltext_Engine

Below is the custom module that I have created to achieve my requirement.
config.xml.

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <modules>
        <Custom_SkuSearch>
            <version>1.0.0</version>
        </Custom_SkuSearch>
    </modules>
    <global>
        <models>
            <skuSearch>
                <class>Custom_SkuSearch_Model</class>
                <resourceModel>skuSearch_resource</resourceModel>
            </skuSearch>
            <skuSearch_resource>
                <class>Custom_SkuSearch_Model_Resource</class>
            </skuSearch_resource>   
        </models>
        <resources>
            <skuSearch_setup>
                <setup>
                    <module>Custom_SkuSearch</module>
                    <class>Custom_SkuSearch_Model_Resource_Setup</class>
                </setup>
            </skuSearch_setup>
        </resources>
        <events>          
            <catalog_product_save_after>
                <observers> 
                    <skuSearch>
                        <type>model</type>
                        <class>Custom_SkuSearch_Model_Observer</class>
                        <method>catalog_product_save_after</method>
                    </skuSearch>
                </observers> 
            </catalog_product_save_after>
        </events>
    </global>
</config>

install-1.0.0.php

<?php

// This installer scripts adds a product attribute to Magento programmatically.
// Set data:
$attributeName = 'Child SKUs'; // Name of the attribute
$attributeCode = 'child_sku'; // Code of the attribute
$attributeGroup = 'General';          // Group to add the attribute to
// Configuration:
$data = array(
    'type' => 'text', // Attribute type
    'input' => 'text', // Input type
    'global' => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_STORE, // Attribute scope
    'required' => false, // Is this attribute required?
    'user_defined' => false,
    'searchable' => true,
    'filterable' => false,
    'comparable' => false,
    'visible_on_front' => true,
    'unique' => false,
    'used_in_product_listing' => true,
    'apply_to' => 'grouped,configurable,bundle',
    'label' => $attributeName
);

// Create attribute:
// We create a new installer class here so we can also use this snippet in a non-EAV setup script.
$installer = Mage::getResourceModel('catalog/setup', 'catalog_setup');
$installer->startSetup();
$installer->addAttribute('catalog_product', $attributeCode, $data);

// Add the attribute to the attribute sets:
$entityType = Mage::getModel('catalog/product')->getResource()->getEntityType();
$collection = Mage::getResourceModel('eav/entity_attribute_set_collection')->setEntityTypeFilter($entityType->getId());

foreach ($collection as $attributeSet) {
    $attributeGroupId = $installer->getDefaultAttributeGroupId('catalog_product', $attributeSet->getId());
    $installer->addAttributeToSet('catalog_product', $attributeSet->getId(), $attributeGroupId, $attributeCode);
}

$installer->endSetup();

Observer.php

<?php

class Custom_SkuSearch_Model_Observer {

    public function catalog_product_save_after($observer) {
        $product = $observer->getProduct();
        $productTypeId = $product->getTypeId();
        if (in_array($productTypeId, array("configurable", "grouped", "bundle"))) {
            if ($productTypeId == "configurable") {
                $skus = array();
                $childProducts = Mage::getModel('catalog/product_type_configurable')->getUsedProducts(null, $product);
                foreach ($childProducts as $child) {
                    $skus[] = $child->getSku();
                }
                $skus = implode(', ', $skus);
            }
        }

        $product->setChildSku($skus);
        $product->getResource()->saveAttribute($product, 'child_sku');
    }

}