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() ) {
$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.