1

I have a collection of associated products of a configurable product. I want to get category for this products. The categories are assigned to their parent product(Configurable Product) and not to the simple products which I have.

I am using below code to add category_id to my collection

$_productCollection->getSelect()->joinLeft('catalog_category_product',
    'e.entity_id = catalog_category_product.product_id',
    array('product_category'=>'category_id'));

It works but for associated products, it shows NULL, I want to check if there is Null value for category_id then use category_id of its Parent Product.

Does anyone have any idea how to get categories from parent product using an SQL query?

1 Answer 1

2

You can use below query to get parent product category id if there is no category assigned to associated products.

$_resultCollection->getSelect()->joinLeft('catalog_category_product',
'e.entity_id = catalog_category_product.product_id',
array('product_category'=>'IFNULL(catalog_category_product.category_id,
(SELECT `catalog_category_product`.`category_id` FROM `catalog_product_super_link` 
LEFT JOIN `catalog_category_product` ON `catalog_category_product`.`product_id` = `catalog_product_super_link`.`parent_id` 
where `catalog_product_super_link`.`product_id` = `e`.`entity_id` 
GROUP BY `catalog_product_super_link`.`product_id`))'));

Your Answer

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

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