Error searching order grid

[paales]

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous

#0 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `main_ta...', Array)
#4 lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main_ta...', Array)
#5 lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `main_ta...', Array)
#6 lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT `main_ta...', Array)
#7 app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll('SELECT `main_ta...', Array)
#8 lib/Varien/Data/Collection/Db.php(566): Mage_Core_Model_Resource_Db_Collection_Abstract->getData()
#9 app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(533): Varien_Data_Collection_Db->load()
#10 app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php(61): Mage_Adminhtml_Block_Widget_Grid->_prepareCollection()
#11 app/code/local/Innoexts/Warehouse/Block/Adminhtml/Sales/Order/Grid.php(64): Mage_Adminhtml_Block_Sales_Order_Grid->_prepareCollection()
#12 app/code/community/BL/CustomGrid/Model/Observer.php(530) : eval()'d code(84): Innoexts_Warehouse_Block_Adminhtml_Sales_Order_Grid->_prepareCollection()
#13 app/code/community/BL/CustomGrid/Model/Observer.php(530) : eval()'d code(365): BL_CustomGrid_Block_Rewrite_Sales_Order_Grid->_prepareCollection()
#14 app/code/community/BL/CustomGrid/Model/Observer.php(912): BL_CustomGrid_Block_Rewrite_Sales_Order_Grid->blcg_finishPrepareCollection()
#15 app/code/community/BL/CustomGrid/Model/Observer.php(530) : eval()'d code(81): BL_CustomGrid_Model_Observer->afterGridPrepareCollection(Object(BL_CustomGrid_Block_Rewrite_Sales_Order_Grid))
#16 app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(626): BL_CustomGrid_Block_Rewrite_Sales_Order_Grid->_prepareCollection()
#17 app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(632): Mage_Adminhtml_Block_Widget_Grid->_prepareGrid()
#18 app/code/core/Mage/Core/Block/Abstract.php(862): Mage_Adminhtml_Block_Widget_Grid->_beforeToHtml()
#19 app/code/core/Mage/Core/Block/Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
#20 app/code/core/Mage/Core/Block/Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml('grid', true)
#21 app/code/core/Mage/Adminhtml/Block/Widget/Grid/Container.php(77): Mage_Core_Block_Abstract->getChildHtml('grid')
#22 app/design/adminhtml/default/default/template/widget/grid/container.phtml(36): Mage_Adminhtml_Block_Widget_Grid_Container->getGridHtml()
#23 app/code/core/Mage/Core/Block/Template.php(241): include('/Users/paales/S...')
#24 app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/defau...')
#25 app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#26 app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#27 app/code/core/Mage/Adminhtml/Block/Widget/Container.php(308): Mage_Adminhtml_Block_Template->_toHtml()
#28 app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Adminhtml_Block_Widget_Container->_toHtml()
#29 app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#30 app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Core_Block_Text_List->_toHtml()
#31 app/code/core/Mage/Core/Block/Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
#32 app/code/core/Mage/Core/Block/Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml('content', true)
#33 app/design/adminhtml/default/default/template/page.phtml(74): Mage_Core_Block_Abstract->getChildHtml('content')
#34 app/code/core/Mage/Core/Block/Template.php(241): include('/Users/paales/S...')
#35 app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/defau...')
#36 app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#37 app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#38 app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Adminhtml_Block_Template->_toHtml()
#39 app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#40 app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#41 app/code/core/Mage/Adminhtml/controllers/Sales/OrderController.php(95): Mage_Core_Controller_Varien_Action->renderLayout()
#42 app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Adminhtml_Sales_OrderController->indexAction()
#43 app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#44 app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#45 app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#46 app/Mage.php(683): Mage_Core_Model_App->run(Array)
#47 index.php(87): Mage::run('', 'store')
#48 {main}

[mage-eag]

Currently the BL_CustomGrid_Helper_Collection::prepareGridCollectionFiltersMap() method is responsible for ensuring that such cases don't happen, by preparing a complete filters map for grids that have custom columns (some of which may join tables having same fields), but it then seems that it can't handle yet grid rewrites coming from third-party extensions, eg here Innoexts_Warehouse (or maybe there is yet another reason).

Could you paste here the grid informations that can be found under "Grid Customization" > "Grid Infos" ? Thanks !

[paales]

Uhm, I don't know which site this happened at :p. I can reproduce a similar error (without a grid rewrite, column is added by the observer).

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_by_role' in where clause is ambiguous

#0 /Users/paales/Sites/mentalsuits.com/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /Users/paales/Sites/mentalsuits.com/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /Users/paales/Sites/mentalsuits.com/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /Users/paales/Sites/mentalsuits.com/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `main_table`.*, `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`shipping_description` AS `blcg_custom_field_1` FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN `sales_flat_order` AS `_blcgmccob_e229fbaa727cc57d1f68c0402db32809` ON `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`entity_id` = `main_table`.`entity_id` WHERE (created_by_role = '1') ORDER BY main_table.created_at DESC LIMIT 20', Array)
#4 /Users/paales/Sites/mentalsuits.com/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main_table`.*, `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`shipping_description` AS `blcg_custom_field_1` FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN `sales_flat_order` AS `_blcgmccob_e229fbaa727cc57d1f68c0402db32809` ON `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`entity_id` = `main_table`.`entity_id` WHERE (created_by_role = '1') ORDER BY main_table.created_at DESC LIMIT 20', Array)
#5 /Users/paales/Sites/mentalsuits.com/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `main_table`.*, `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`shipping_description` AS `blcg_custom_field_1` FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN `sales_flat_order` AS `_blcgmccob_e229fbaa727cc57d1f68c0402db32809` ON `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`entity_id` = `main_table`.`entity_id` WHERE (created_by_role = '1') ORDER BY main_table.created_at DESC LIMIT 20', Array)
#6 /Users/paales/Sites/mentalsuits.com/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT `main_table`.*, `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`shipping_description` AS `blcg_custom_field_1` FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN `sales_flat_order` AS `_blcgmccob_e229fbaa727cc57d1f68c0402db32809` ON `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`entity_id` = `main_table`.`entity_id` WHERE (created_by_role = '1') ORDER BY main_table.created_at DESC LIMIT 20', Array)
#7 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll('SELECT `main_table`.*, `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`shipping_description` AS `blcg_custom_field_1` FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN `sales_flat_order` AS `_blcgmccob_e229fbaa727cc57d1f68c0402db32809` ON `_blcgmccob_e229fbaa727cc57d1f68c0402db32809`.`entity_id` = `main_table`.`entity_id` WHERE (created_by_role = '1') ORDER BY main_table.created_at DESC LIMIT 20', Array)
#8 /Users/paales/Sites/mentalsuits.com/lib/Varien/Data/Collection/Db.php(566): Mage_Core_Model_Resource_Db_Collection_Abstract->getData()
#9 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(533): Varien_Data_Collection_Db->load()
#10 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php(61): Mage_Adminhtml_Block_Widget_Grid->_prepareCollection()
#11 /Users/paales/Sites/mentalsuits.com/.modman/BL_CustomGrid/app/code/community/BL/CustomGrid/Model/Observer.php(530) : eval()'d code(84): Mage_Adminhtml_Block_Sales_Order_Grid->_prepareCollection()
#12 /Users/paales/Sites/mentalsuits.com/.modman/BL_CustomGrid/app/code/community/BL/CustomGrid/Model/Observer.php(530) : eval()'d code(365): BL_CustomGrid_Block_Rewrite_Sales_Order_Grid->_prepareCollection()
#13 /Users/paales/Sites/mentalsuits.com/.modman/BL_CustomGrid/app/code/community/BL/CustomGrid/Model/Observer.php(912): BL_CustomGrid_Block_Rewrite_Sales_Order_Grid->blcg_finishPrepareCollection()
#14 /Users/paales/Sites/mentalsuits.com/.modman/BL_CustomGrid/app/code/community/BL/CustomGrid/Model/Observer.php(530) : eval()'d code(81): BL_CustomGrid_Model_Observer->afterGridPrepareCollection(BL_CustomGrid_Block_Rewrite_Sales_Order_Grid)
#15 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(626): BL_CustomGrid_Block_Rewrite_Sales_Order_Grid->_prepareCollection()
#16 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(632): Mage_Adminhtml_Block_Widget_Grid->_prepareGrid()
#17 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Abstract.php(862): Mage_Adminhtml_Block_Widget_Grid->_beforeToHtml()
#18 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
#19 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml('grid', true)
#20 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/Block/Widget/Grid/Container.php(77): Mage_Core_Block_Abstract->getChildHtml('grid')
#21 /Users/paales/Sites/mentalsuits.com/app/design/adminhtml/default/default/template/widget/grid/container.phtml(36): Mage_Adminhtml_Block_Widget_Grid_Container->getGridHtml()
#22 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Template.php(241): include('/Users/paales/Sites/mentalsuits.com/app/design/adminhtml/default/default/template/widget/grid/container.phtml')
#23 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/default/default/template/widget/grid/container.phtml')
#24 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#25 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#26 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/Block/Widget/Container.php(308): Mage_Adminhtml_Block_Template->_toHtml()
#27 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Adminhtml_Block_Widget_Container->_toHtml()
#28 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#29 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Core_Block_Text_List->_toHtml()
#30 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
#31 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml('content', true)
#32 /Users/paales/Sites/mentalsuits.com/app/design/adminhtml/default/default/template/page.phtml(74): Mage_Core_Block_Abstract->getChildHtml('content')
#33 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Template.php(241): include('/Users/paales/Sites/mentalsuits.com/app/design/adminhtml/default/default/template/page.phtml')
#34 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/default/default/template/page.phtml')
#35 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#36 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#37 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Adminhtml_Block_Template->_toHtml()
#38 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#39 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#40 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Adminhtml/controllers/Sales/OrderController.php(95): Mage_Core_Controller_Varien_Action->renderLayout()
#41 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Adminhtml_Sales_OrderController->indexAction()
#42 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#43 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Mage_Core_Controller_Request_Http)
#44 /Users/paales/Sites/mentalsuits.com/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#45 /Users/paales/Sites/mentalsuits.com/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#46 /Users/paales/Sites/mentalsuits.com/index.php(87): Mage->run('', 'store')

The created_by_role column/attribute is present in the products table, customer table, invoice table and order table.

When viewing the orders page everything is fine, when filtering a normal column, everything is fine. When filtering without ever having set up the custom grid I can filter the created_by_role column. After adding a order column (payment method in this instance) and when I filter on the created_by_role column the page breaks with the above error.

My first guess was that I added the column not as needly as I should, this is my addColumn code and my code to add it to the collection:

$block->addColumn('created_by_role', array(
    'header' => Mage::helper('sales')->__('Created By Role'),
    'index' => 'created_by_role',
    'type'  => 'options',
    'options' => Mage::getModel('ho_permissions/attribute_source_createdbyrole')->toOptionHash()
));

But I can't really find anything wrong with / don't know how to set the table.

[mage-eag]

Is the created_by_role column actually present in both the sales_flat_order_grid and sales_flat_order tables, as it seems to be ?
If so, therefore once it is filtered when one or more custom columns that relies on sales_flat_order are added to the grid, it crashes if the corresponding filter is not prefixed with a proper table alias (until now, only the "original" grid fields were added to the filters map by the helper I mentioned earlier).
You would have two solutions to fix this : if the created_by_role is present in sales_flat_order_grid, then you can set the filter_index value to main_table.created_by_role in your observer.
Or, in all cases, you could add it via a custom column instead of an observer, with an etc/customgrid.xml file in your extension (not tested) :

<?xml version="1.0"?>
<customgrid>
    <grid_types>
        <sales_order>
            <custom_columns>
                <hop_created_by_role model="customgrid/custom_column_order_base" module="customgrid">
                    <name>Created By Role</name>
                    <group>Permissions</group>
                    <allow_renderers>1</allow_renderers>
                    <model_params>
                        <order_field>created_by_role</order_field>
                    </model_params>
                </hop_created_by_role>
            </custom_columns>
        </sales_order
    </grid_types>
</customgrid>

Regarding the options filter, you could either use the "Options" renderer along with an options source set to your model (ho_permissions/attribute_source_createdbyrole), or use instead a custom class inheriting from customgrid/custom_column_order_base, and forcing the type and options values.

Anyway, the new 1b94e4c commit brought a new experimental feature that should prevent almost all the problems of this kind, by detecting which applied filters may lead to an ambiguous column, and search in the joined tables for a proper alias to prepend to them. However it still need to be tested in various cases, so tell me what it gives for you :)

[paales]

Is the created_by_role column actually present in both the sales_flat_order_grid and sales_flat_order tables, as it seems to be ?

Indeed it is, that is probably what is causing the SQL error.

You would have two solutions to fix this : if the created_by_role is present in sales_flat_order_grid, then you can set the filter_index value to main_table.created_by_role in your observer.

This fixes it.

Or, in all cases, you could add it via a custom column instead of an observer, with an etc/customgrid.xml file in your extension (not tested)

Cool :) 👍

Anyway, the new 1b94e4c commit brought a new experimental feature that should prevent almost all the problems of this kind, by detecting which applied filters may lead to an ambiguous column, and search in the joined tables for a proper alias to prepend to them. However it still need to be tested in various cases, so tell me what it gives for you :)

Will test this later on, it is fixed for now.