php - Magento - 加载管理面板页面时出现 MySQL 错误

Question

我收到以下错误:

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)

如果我猜对了，它在 sales_flat_order 表中找不到 web4pro_abandonedcart_flag 列，对吗？

但是我已经检查过了，我 100% 确定该表中有这样的列。

这是造成此故障的函数:

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();
}

请问问题出在哪里吗？

Answer 1

是的，给定字段可能存在于给定表中，但是，外部查询的 from 子句中未引用 sales_flat_order 表，但在 其中条件是您引用此表中的字段。

您的查询以简化方式如下所示:

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

在外部查询中，从字段名称中删除 sales_flat_order，或将其替换为 main_table。但最好的解决方案是完全摆脱子查询。