Display custom billing address checkout fields in admin sales order grid

Question

In my custom module, I have modified the checkout process a bit and added some custom fields in billing address section following the below tutorial:

http://excellencemagentoblog.com/blog/2011/10/06/magento-add-custom-fields-checkout-page/

Now, these custom fields here are saved in a different table in the form of a key=>value pair, based on order_id in sales_order_custom table created by my module itself.

Further, I want to get these values associated with the sales_order module and display some of these key=>value kind of fields in admin sales order grid.

How how do I join these two tables so that the keys also get converted into columns and I can retrieve them directly as "addColumn" index??

I tried following links, but I think they are somewhat not the right way I am approaching..

1) https://plugin.company/blog/join-eav-attribute-flat-table-collection-magento/ - trying this, I got this error : "Invalid entity_type specified: sales_order_custom"

2)http://blog.ki6i.com/magento-join-eav-and-flat-table/

Thanks in advance..

Answer 1

My article refers to joining an 'standard' Magento EAV attribute/table to a flat table in Magento.

What you are trying to do is join a custom table to a flat table.

Something like the following should do the trick:

$collection = Mage::getModel('sales/order')->getCollection();
$collection
       ->getSelect()
       ->joinLeft(
              array('customattribute1' => 'sales_order_custom'),
              "customattribute.order_id=main_table.entity_id AND customattribute.key = 'keynumber1here'"
               )
          )
        ;

(replace keynumber1here with the key you'd like to add)

Then you could do the following in the grid:

$this->addColumn('your_column_name',array(
     'label' => $this->__('Your Label'),
     'index' => 'customattribute1.value'
    )
)

Hope this helps!