Tony Stark Tony Stark - 1 month ago 6
PHP Question

Magento - MySQL error while loading an admin panel page

I am receiving the following error:

a:5:{i:0;s:2328:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_flat_order.web4pro_abandonedcart_flag' in 'where clause', query was: SELECT COUNT(*) FROM (SELECT `main_table`.*, `sales_flat_order`.`web4pro_abandonedcart_flag`, GROUP_CONCAT(order_item_tbl.`name` SEPARATOR '\n') AS `product_names`, GROUP_CONCAT(order_item_tbl.`sku` SEPARATOR '\n') AS `skus`, GROUP_CONCAT(order_item_tbl.`product_id` SEPARATOR '\n') AS `product_ids`, GROUP_CONCAT(order_item_tbl.`product_options` SEPARATOR '^') AS `product_options`, `order_address_billing_tbl`.`company` AS `billing_company`, `order_address_billing_tbl`.`street` AS `billing_street`, `order_address_billing_tbl`.`city` AS `billing_city`, `order_address_billing_tbl`.`region` AS `billing_region`, `order_address_billing_tbl`.`country_id` AS `billing_country_id`, `order_address_billing_tbl`.`postcode` AS `billing_postcode`, `order_address_billing_tbl`.`telephone` AS `billing_telephone`, `order_address_shipping_tbl`.`company` AS `shipping_company`, `order_address_shipping_tbl`.`street` AS `shipping_street`, `order_address_shipping_tbl`.`city` AS `shipping_city`, `order_address_shipping_tbl`.`region` AS `shipping_region`, `order_address_shipping_tbl`.`country_id` AS `shipping_country_id`, `order_address_shipping_tbl`.`postcode` AS `shipping_postcode`, `order_address_shipping_tbl`.`telephone` AS `shipping_telephone`, `order_payment_tbl`.`method` FROM `sales_flat_order_grid` AS `main_table`
INNER JOIN `sales_flat_order` ON main_table.increment_id = sales_flat_order.increment_id
LEFT JOIN `sales_flat_order_item` AS `order_item_tbl` ON `order_item_tbl`.`order_id` = `main_table`.`entity_id` AND `order_item_tbl`.`parent_item_id` IS NULL
LEFT JOIN `sales_flat_order_address` AS `order_address_billing_tbl` ON order_address_billing_tbl.parent_id = main_table.entity_id AND order_address_billing_tbl.`address_type` = "billing"
LEFT JOIN `sales_flat_order_address` AS `order_address_shipping_tbl` ON order_address_shipping_tbl.parent_id = main_table.entity_id AND order_address_shipping_tbl.`address_type` = "shipping"
LEFT JOIN `sales_flat_order_payment` AS `order_payment_tbl` ON order_payment_tbl.parent_id = main_table.entity_id GROUP BY `main_table`.`entity_id`) AS `main_table` WHERE (`sales_flat_order`.`web4pro_abandonedcart_flag` = 1) AND (order_group_id = '0')";i:1;s:5789:"#0 /home/*********/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)


If i get it right it is not finding
web4pro_abandonedcart_flag
column in
sales_flat_order
table right ?

However i have checked and i am 100% sure that there is such column in that table.

Here is the function which creates this malfunction:

protected function _prepareCollection()
{
$collection = Mage::getResourceModel('sales/order_grid_collection');
$this->setCollection($collection);
$sales_flat_order_table = Mage::getSingleton('core/resource')->getTableName('sales_flat_order');
$collection->getSelect()->join($sales_flat_order_table , 'main_table.increment_id = '.$sales_flat_order_table.'.increment_id', 'web4pro_abandonedcart_flag');
$collection->addFieldToFilter($sales_flat_order_table.'.web4pro_abandonedcart_flag',array('eq' => 1));
return parent::_prepareCollection();
}


Can you please advice where the problem can be ?

Answer

Yes, the given field may exist in the given table, however, the sales_flat_order table is not referenced in the outer query's from clause, yet in the where criteria you are referencing a field from this table.

Your query in a simplified way looks like below:

SELECT COUNT(*)
FROM (...) AS `main_table`
WHERE (`sales_flat_order`.`web4pro_abandonedcart_flag` = 1) AND (order_group_id = '0')

In the outer query either remove sales_flat_order from the field name, or replace it by main_table. But the best solution would be to get rid of the subquery completely.