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.