How to convert below MySql Query to Magento collection :

I trying to show, which product have less then 50% of Final Prices in product Price, should be shown in web page.

I have tried below query working fine in MySql its working fine, I need to convert in Magento collection format.

SELECT `e`.*, `at_category_id`.`category_id`, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`, IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`, `at_special_price`.`value` AS `special_price`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
 LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=e.entity_id)
 INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '273') AND `at_status_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '273') AND (`at_status`.`store_id` = 1)
 INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '526') AND `at_visibility_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '526') AND (`at_visibility`.`store_id` = 1)
 INNER JOIN `catalog_product_entity_decimal` AS `at_special_price` ON (`at_special_price`.`entity_id` = `e`.`entity_id`) AND (`at_special_price`.`attribute_id` = '567') AND (`at_special_price`.`store_id` = 0) 
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1') AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) = '4') AND (at_special_price.value != '') AND (at_category_id.category_id = '35') and price_index.final_price < (price_index.price * .5)


  • Just to make it clear, you want all the products froma specific category that has the final price less than 50% of the original price? – Marius May 19 '15 at 13:21
  • @Marius Exact one, I need that collection. – V-jay May 19 '15 at 13:22
  • @Marius i tried above Mysql Query is working fine, i am trying to convert Magento Collection format. Help me. – V-jay May 19 '15 at 13:34

I don't know if this is the exact translation of your query to a collection, but this should get you all the products with a at least 50% discount from a certain category:

$categoryId = 35;
/** @var Mage_Catalog_Model_Category $category */
$category = Mage::getModel('catalog/category')->load($categoryId);
/** @var Mage_Catalog_Model_Resource_Product_Collection $collection */
$collection = $category->getProductCollection();
$collection->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())//you can replace this with addAttributeToSelect('*')
    ->addUrlRewrite($category->getId()); //this is optional. Needed only if you list products and you want to provide a link to them
$collection->addExpressionAttributeToSelect('discount_percent', '(({{price}} - final_price) * 100 / {{price}})', array('price'));
$collection->getSelect()->having('discount_percent >= 50');

This will take into account everything that affects the price: special price, catalog price rules and other logic you might have.

If you want to get only the products that have the special price 50% or less from the base price replace the line

$collection->addExpressionAttributeToSelect('discount_percent', '(({{price}} - final_price) * 100 / {{price}})', array('price'));


$collection->addExpressionAttributeToSelect('discount_percent', '(({{price}} - {{special_price}}) * 100 / {{price}})', array('price', 'special_price'));

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.