Convert SQL Select to Magento getCollection statement

Question

I have a complex sql select query that I want to load as collection so as to display the data in a custom grid but I'm not able to convert the query into Magento's equivalent getCollection statement.

SELECT
t1.increment_id AS "Order #",
t1.created_at as "Purchased On",
t1.billing_name as "Bill to Name",
t1.shipping_name as "Ship to Name",
t1.status as "Current Status",
IFNULL(MAX(CASE WHEN t2.status = "pending" THEN t2.created_at END), '') AS "Pending at",
IFNULL(MAX(CASE WHEN t2.status = "processing" THEN t2.created_at END), '') AS "Processing at"
FROM `sales_flat_order_grid` as t1
INNER JOIN `sales_flat_order_status_history` as t2 on t1.entity_id = t2.parent_id
GROUP BY t1.increment_id
ORDER BY t1.created_at DESC

Joins are easy but the main problem are the complex columns. Please suggest on how to go about it.

Answer 1

Something like this should do the trick:

$table = Mage::getSingleton('core/resource')->getTableName('sales/order_status_history');
$collection = Mage::getResourceModel('sales/order_grid_collection');
$select = $collection
 ->getSelect()
 ->join(array('t2' => $table), 'main_table.entity_id = t2.parent_id', array())
 ->columns(array(
 "Order #" => 'increment_id',
 "Purchased On" => 'created_at',
 "Bill to Name" => 'billing_name',
 "Ship to Name" => 'shipping_name',
 "Current Status" => 'status',
 ))
 ->columns('IFNULL(MAX(CASE WHEN t2.status = "pending" THEN t2.created_at END), "") AS "Pending at"')
 ->columns('IFNULL(MAX(CASE WHEN t2.status = "processing" THEN t2.created_at END), "") AS "Processing at"')
 ->group('increment_id')
 ->order('created_at DESC');

• Anil Kumar: Awesome! This works flawlessly. Thanks @cags
• Peter O'Callaghan: Flawlessly once edited, oops, silly copy paste :)

Answer 2

Find Category Level into product:-

SELECT level,category_id,product_id FROM catalog_category_product,catalog_category_entity where catalog_category_entity.entity_id = catalog_category_product.category_id ORDER BY product_id,level ASC

""Convert this query into Magento format""