I have a complex SQL Select query that I want to convert to Magento query. It is for index of Bestseller. But I am not able to convert the SQL Select into Magento's Query. Please Help!

SELECT SUM( qty_ordered ) AS ordered_qty, store_id AS order_items_name, IF( parent_id IS NOT NULL AND visibility !=4, parent_id, product_id ) AS final_product_id FROM ( SELECT order_items.qty_ordered, order_items.name, order_items.product_id, cpr.parent_id, order_items.store_id, cat_index.visibility, cat_index.category_id FROM sales_flat_order_item AS order_items INNER JOIN sales_flat_order AS order ON order.entity_id = order_items.order_id AND order.state != 'canceled' LEFT JOIN catalog_product_relation AS cpr ON cpr.child_id = order_items.product_id LEFT JOIN catalog_category_product_index AS cat_index ON cat_index.product_id = order_items.product_id WHERE parent_item_id IS NULL AND cat_index.store_id =1 AND category_id =2 ) AS T1 GROUP BY final_product_id, store_id ORDER BY final_product_id;

1 Answer 1


Custom Queries work like these:

$write = Mage::getSingleton('core/resource')->getConnection('core_write');

// now $write is an instance of Zend_Db_Adapter_Abstract
$readresult=$write->query("SELECT *
FROM `tableName`
ORDER BY `anyField` DESC
LIMIT 0 , 30 ");

while ($row = $readresult->fetch() ) {

`//Further you can insert like

 foreach ($Ids as $entity_id) {
$write->query( 'INSERT INTO cataloginventory_stock_item
VALUES ( '.$_id.', 1, 99999, 1)' );

You can alter the query as per you need.

Your Answer

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

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