2

I want my query to filter only distinct product_id from the table catalog_category_product . The raw sql query goes like this :

SELECT * FROM catalog_category_entity AS catalog_category_table 
LEFT JOIN (SELECT DISTINCT(product_id),category_id FROM catalog_category_product GROUP BY product_id) catalog_category_product_table ON catalog_category_product_table.category_id = catalog_category_table.entity_id 

can anyone suggest how to write the sub query while performing the left join magento way. Thanks in advance

3

You can try to do something like this:

$subquery = new Zend_Db_Expr("( 
    SELECT DISTINCT(product_id), category_id 
    FROM `catalog_category_product` 
    GROUP BY product_id
)");
$collection = Mage::getModel('catalog/category')->getCollection();
$collection->getSelect()
    ->joinLeft(
        $subquery, 
        't.category_id = e.entity_id'
    );

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.