Magento - SQLSTATE[42S22]: Column not found: 1054 Unknown column 'billing_name' in 'where clause'

Question

I've change app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php to customize what's on the Orders grid.

In _getCollectionClass() I have this:

protected function _getCollectionClass()
{
    //return 'sales/order_grid_collection';
    return 'sales/order_collection';
}

and in _prepareCollection() I have this:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());

    $collection->getSelect()->joinLeft(array('s1' => 'sales_flat_order_address'),'main_table.shipping_address_id = s1.entity_id',array('region','firstname','lastname'));
    $collection->getSelect()->joinLeft(array('s2'=>'sales_flat_order_address'),'main_table.billing_address_id = s2.entity_id',array('firstname','lastname'));

    $collection->getSelect()->columns(new Zend_Db_Expr("CONCAT(s2.firstname, ' ',s2.lastname) AS billing_name"));
    $collection->getSelect()->columns(new Zend_Db_Expr("CONCAT(s1.firstname, ' ',s1.lastname) AS shipping_name"));

$collection->getSelect()->joinLeft(array('sfo'=>'sales_flat_order'),'sfo.entity_id=main_table.entity_id',array('sfo.customer_email','sfo.weight','sfo.discount_description','sfo.increment_id','sfo.store_id','sfo.created_at','sfo.status','sfo.base_grand_total','sfo.grand_total')); // New 
    $collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street','sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone')); // New


    $this->setCollection($collection);

    return parent::_prepareCollection();
}

Now I've changed the _prepareColumns() to add my necessary fields and all works great! Except for one thing...

When I search for orders by Billing or Shipping, I get an error. I've added filter_index's ('filter_index' => 'theindex') to all the necessary components and they all work great EXCEPT those two fields Billing or Shipping.

So I put filter_index's on those too.

Everything comes up great. I can search the other fields but as soon as I search the Billing or Shipping fields, I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'billing_name' in 'where clause'

I have tried all sorts of things but nothing seems to work. Can someone please help!???

---

Solution

When I took a step back for a minute and just started looking at the db, it dawned on me that the fields I want are already formated. I do not need to concatenate/merge anything. I just simply needed to call the fields like the other ones were already being called...

My new _prepareCollection() function is:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());

    $collection->getSelect()->joinLeft(array('sfog' => 'sales_flat_order_grid'),'main_table.entity_id = sfog.entity_id',array('sfog.shipping_name','sfog.billing_name'));

$collection->getSelect()->joinLeft(array('sfo'=>'sales_flat_order'),'sfo.entity_id=main_table.entity_id',array('sfo.customer_email','sfo.weight','sfo.discount_description','sfo.increment_id','sfo.store_id','sfo.created_at','sfo.status','sfo.base_grand_total','sfo.grand_total')); // New 
    $collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street','sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone')); // New


    $this->setCollection($collection);

    return parent::_prepareCollection();
}

Notice the sfog array.

This goes into the sales_flat_order_grid table of the database and grabs the preformatted names as they were in the original grid. I wonder if this has anything to do with the fact that the original grid was being called from this table (sarcasm) - :P

Then, like just make sure you do the filter_index in the two name fields (like all the others)

Example:

$this->addColumn('billing_name', array(
        'header' => Mage::helper('sales')->__('Bill to Name'),
        'index' => 'billing_name',
        'filter_index' => 'sfog.billing_name',
    ));

And thats all she wrote!