addColumn to sales_flat_order_grid table with CASE data

Question

Using Magento ver. 1.9.0.1

Using this amazingly well written answer "Add Column to a grid (observer) - Column ‘store_id’ in where clause is ambiguous issue" I have added new basic columns to the sales_flat_order_grid table.

However, the next part of my requirements has me stumped! I am trying to add a new column to the sales_flat_order_grid table with data manipulated using CASE MYSQL. The idea is that a new column is added showing when an order has been paid, part paid or still awaiting payment based on data from the sales_flat_order_payment table.

In MYSQL the query would look like this:

SELECT `order_grid`.*,
CASE
WHEN `order`.`total_paid` > `order`.`grand_total` AND `order`.`total_paid` > 0 THEN 'part_paid'
WHEN `order`.`total_paid` = `order`.`grand_total` THEN 'paid'
ELSE 'not_paid'
END AS `payment_status`
FROM `sales_flat_order_grid` AS `order_grid`
LEFT JOIN `sales_flat_order` AS `order`
ON `order`.`entity_id` = `order_grid`.`entity_id`

I was able to work out that the Zend equivalent looks something like this:

/*
=================
Code sample from modified version of
/app/code/community/Example/SalesGrid/sql/example_salesgrid_setup/install-0.1.0.php
(See linked example above)
=================
*/

$adapter = $this->getConnection();
$select = $adapter->select();

$case_payment_status = $adapter->getCaseSql('', array(
    'order_payment.amount_paid  0' => '"part_paid"',
    'order_payment.amount_paid = order_payment.amount_ordered' => '"paid"'
), '"not_paid"');

$select->join(
    array('order_payment'=>$this->getTable('sales/order_payment')),
    $this->getConnection()->quoteInto(
        'order_payment.parent_id = order_grid.entity_id'
    ),
    array(
        'payment_status' => $case_payment_status
    )
);

I know this code works as the new payment_status column is correctly populated with data from this case on installation/upgrade /app/code/community/Example/SalesGrid/sql/example_salesgrid_setup/install-0.1.0.php

 

# Question #

My question is, how can I add this new CASE created payment_status column to the resource using the addVirtualGridColumn? The code below gives an unknown error on order update/save...

class Example_SalesGrid_Model_Observer {

    public function addColumnToResource(Varien_Event_Observer $observer) {
        // Only needed if you use a table other than sales/order (sales_flat_order)

        $resource = $observer->getEvent()->getResource();

        $adapter = $resource->getReadConnection();
        $case_payment_status = $adapter->getCaseSql('', array(
            '{{table}}.amount_paid  0' => '"part_paid"',
            '{{table}}.amount_paid = {{table}}.amount_ordered' => '"paid"'
        ), '"not_paid"');

        $resource->addVirtualGridColumn(
            'payment_status',
            'sales/order_payment',
            array('parent_id' => 'entity_id'),
            $case_payment_status
        );
    }
}

I really hope someone can help or point me in the right direction as I think the solution is very close but can't quite reach it alone.

Please let me know if you have questions of if I've not explained the issue well enough.

Thanks in advance!

Answer 1

Sorted! In case anyone is interested in the solution, follow the steps laid out in the linked answer, but in the observer:

class Example_SalesGrid_Model_Observer {

    public function addColumnToResource(Varien_Event_Observer $observer) {
        // Only needed if you use a table other than sales/order (sales_flat_order)

        $resource = $observer->getEvent()->getResource();
        $adapter = $resource->getReadConnection();

        // Define the CASE Statement
        // ------------------------
        $casePaymentStatus = $adapter->getCaseSql('', array(
            '{{table}}.amount_paid  0' => '"part_paid"',
            '{{table}}.amount_paid = {{table}}.amount_ordered' => '"paid"',
        ), '"not_paid"');



        // Insert the CASE Statement into the getConcatSql() Method
        // ------------------------
        $concatPaymentStatus = $adapter->getConcatSql(array($casePaymentStatus));



        // Add the Virtual Grid Column
        // ------------------------
        $resource->addVirtualGridColumn(
            'payment_status',
            'sales/order_payment',
            array('entity_id' => 'parent_id'),
            $concatPaymentStatus
        );
    }
}