0

I have tried below 3 method but it is not giving correct products as per cateory id array.

First Way

$_productCollection = Mage::getResourceModel('catalog/product_collection')
                    ->addFieldToFilter('status',Mage_Catalog_Model_Product_Status::STATUS_ENABLED)
                    ->joinField('is_in_stock', 'cataloginventory/stock_item', 'is_in_stock', 'product_id=entity_id', 'is_in_stock=1', '{{table}}.stock_id=1', 'left')
                    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
                    ->addAttributeToFilter('condition', array('eq' => 814))
                    ->addAttributeToFilter('ep_status',array('in'=>array(820,822)))
                    ->addAttributeToFilter('manufacturer',array('in'=>$manufactId))->addAttributeToSelect('*');
$_productCollection->addAttributeToFilter('category_id', array('in' =>  array('finset' => $catManufacturers )));
$_productCollection->getSelect()->group('entity_id')->distinct(true);

Second Way

$_productCollection = Mage::getModel('catalog/product')->getCollection()         
                    ->addAttributeToFilter('condition', array('eq' => 814))
                    ->addAttributeToFilter('ep_status',array('in'=>array(820,822)))
                    ->addAttributeToFilter('manufacturer',array('in'=>$manufactId))->addAttributeToSelect('*');
if(!empty($catManufacturers)){
    foreach ($catManufacturers as $k => $index) {
        $name = 'category_id_'.$k;
        $_productCollection->joinField($name, 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
            ->addAttributeToFilter($name, array('eq' => $index));
    }
}
$_productCollection->getSelect()->group('entity_id')->distinct(true);

Third Way

$_productCollection =  Mage::getResourceModel('catalog/product_collection')
                    ->addFieldToFilter('visibility', 4)
                    ->addAttributeToFilter('ep_status',array('in' => array(820,822)))
                    ->addAttributeToFilter('manufacturer',array('in'=>$manufactId))
                    ->addAttributeToSelect('*')
                    ->joinField('is_in_stock', 'cataloginventory/stock_item', 'is_in_stock', 'product_id=entity_id', 'is_in_stock=1', '{{table}}.stock_id=1', 'left')
                    ->addFieldToFilter('condition', array('in' => array(814)));
                $_productCollection->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')
                    ->addAttributeToFilter('category_id', array('in' => $catManufacturers));
                $_productCollection->getSelect()->distinct(true);
                $_productCollection->getSelect()->group('entity_id');
2
  • can you please explain more about your problem Jan 8, 2021 at 16:20
  • 1
    @PushpendraSingh I am getting all category products instead of the category passed in array
    – Yogita
    Jan 8, 2021 at 17:57

1 Answer 1

0

You need to do an inner join instead of a left join on catelog_category_product table. Picking Method #3 just because, but you should be able to adapt this to whichever one you want.

$_productCollection->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')->addAttributeToFilter('category_id', array('in' => $catManufacturers));
becomes
$catSql = $_productCollection->getConnection()->prepareSqlCondition('category_id', ['in' => $catManufacturers]); $_productCollection->getSelect()->join(['cp' => $_productCollection->getTable('catalog/category_product')], 'e.entity_id = cp.product_id AND ' . $catSql)); Also a quick note, if you want to view the SELECT in plain sql: $sql = $_productCollection->getSelect()->assemble(); so you can at least see the sql you are trying to run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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