Convert SQL Select to Magento SQL query

Question

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;

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() ) {
$Ids[]=$row['id'];
}

`//Further you can insert like

 foreach ($Ids as $entity_id) {
$write->query( 'INSERT INTO cataloginventory_stock_item
(`product_id`,`stock_id`,`qty`,`is_in_stock`)
VALUES ( '.$_id.', 1, 99999, 1)' );
} 

You can alter the query as per you need.