9

I have situation like 2 attribute which is assign to child product

1) person_height_from and 2) person_height_to which has DropDown type attribute

which is only assign in child product but not assign in parent products,

I want to filter product collection in category page with this attribute

like length = 175

 $collection->addAttributeToFilter('person_height_from', array('lteq' => $length));

 $collection->addAttributeToFilter('person_height_to', array('gteq' => $length));

Is this possible to get only those parent product in category page which child product assign above value

your help will be appreciated

2 Answers 2

0

Please use below code:

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter(
        array(
            array('attribute'=> 'length', 'like' => '175')
        )
    );

$collection->getSelect()
    ->joinLeft(
        array('link_table' => 'catalog_product_super_link'),
        'link_table.product_id = e.entity_id',
        array('product_id', 'parent_id')
    );

$collection->getSelect()->group('link_table.parent_id');

foreach ($collection as $product) {
    $productIds[] = $product->getParentId();
}

$productCollection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter('entity_id', array('in' => $productIds));

In above code first of all I have find child products which have length is 175. After that I have find parent id associated with child product then filter product collection according to parent product Ids.

2
  • 1
    is this possible to do in only 1 sql query, i have more then 1 lac product in store, it will huge effect on performance to get this collection :(
    – liyakat
    Dec 21, 2016 at 11:30
  • it's only for child of configurable product, not include bundle & group
    – huykon225
    May 30, 2021 at 3:06
0

Here i did with 2 attribute of child products to search with inner query

$collection = $observer->getEvent()->getCollection();

// check if  query is already in sql then no need to add per event
$cloneSelect = clone $collection->getSelect();
$wherePart = $cloneSelect->getPart(Zend_Db_Select::WHERE);
$excludedWherePart = 'AND (e.entity_id IN';
foreach ($wherePart as $key => $wherePartItem) {
    if (strpos($wherePartItem, $excludedWherePart) !== false) {
        return $this;
    }
}

$resource = Mage::getSingleton('core/resource');
$_readAdapter = $resource->getConnection('core_read');
$status = Mage::getSingleton('catalog/config')->getAttribute('catalog_product', 'status');
$heightTo = Mage::getSingleton('catalog/config')->getAttribute('catalog_product', 'person_height_to');
$heightFrom = Mage::getSingleton('catalog/config')->getAttribute('catalog_product', 'person_height_from');
$categoryId = Mage::registry("current_category")->getId();
$storeId = Mage::app()->getStore()->getId();

$query = $_readAdapter->select()
    ->from(array('e' => $collection->getTable('catalog/product')),array())
    ->joinInner(array(
        'at_status' => $status->getBackendTable()),
        'at_status.entity_id = e.entity_id AND at_status.store_id = 0 AND at_status.attribute_id = ' .$status->getAttributeId(),
        array()
    )
    ->joinInner(array(
        'at_stock' => $resource->getTableName('cataloginventory/stock_item')),
        'at_stock.product_id = e.entity_id AND at_stock.is_in_stock = ' . 1,
        array()
    )
    ->joinInner(array(
        'cat_index' => $collection->getTable('catalog/category_product_index')),
        'cat_index.product_id = e.entity_id AND cat_index.store_id = ' . $storeId . ' AND cat_index.category_id = ' . $categoryId,
        array()
    )
    ->joinInner(array(
        'at_person_height_to' => $heightTo->getBackendTable()),
        'at_person_height_to.entity_id = e.entity_id AND at_person_height_to.store_id = 0 AND at_person_height_to.attribute_id = ' . $heightTo->getAttributeId(),
        array()
    )
    ->joinInner(array(
        'at_person_height_from' => $heightTo->getBackendTable()),
        'at_person_height_from.entity_id = e.entity_id AND at_person_height_from.store_id = 0 AND at_person_height_from.attribute_id = ' . $heightFrom->getAttributeId(),
    array()
    )
    ->where(
        "e.type_id = 'simple' AND at_person_height_from.value <= " . $length . " AND at_person_height_to.value >= " . $length
    )
    ->join(
        array('link_table' => 'catalog_product_super_link'),
        'link_table.product_id = e.entity_id', array('parent_id')
    );

$productIds =  array_map('intval', array_unique($_readAdapter->fetchCol($query)));

// add filter here with parent ids
$collection->addAttributeToFilter('entity_id', array('in' => $productIds));

Hope this will help to others and get better idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.