MySql Query Convert to Magento Collection Format

Question

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)

Thanks.

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
– Marius, Commented May 19, 2015 at 13:21
@Marius i tried above Mysql Query is working fine, i am trying to convert Magento Collection format. Help me. — V-jay
– V-jay, Commented May 19, 2015 at 13:34

Marius · Accepted Answer · 2015-05-19 13:39:26Z

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('*')
    ->addMinimalPrice()
    ->addFinalPrice()
    ->addTaxPercents()
    ->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');

Note:
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'));

with

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

Stack Exchange Network

MySql Query Convert to Magento Collection Format

1 Answer 1

Your Answer

Hot Network Questions

MySql Query Convert to Magento Collection Format

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions