3

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.

6

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');
  • Awesome! This works flawlessly. Thanks @cags – Anil Jaiswal Mar 13 '15 at 13:14
  • 1
    Flawlessly once edited, oops, silly copy paste :) – Peter O'Callaghan Mar 13 '15 at 15:10
0

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""

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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