I am trying to get the order count on customer grid
Grid Code:
namespace TestCustomerGridModelPluginResourceModelCustomer;
class Grid
{
public static $table = 'customer_grid_flat';
public static $leftJoinTable = 'sales_order';
public function afterSearch($intercepter, $collection)
{
if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {
$readAdapter = $collection->getConnection();
$select = $readAdapter->select()
->from('sales_order_grid')
->where('status!=?', '')
->columns(array(
'orders_total' => new Zend_Db_Expr('CEIL(SUM(grand_total))'),
'orders_count' => new Zend_Db_Expr('COUNT(*)')
)
)->group('customer_id');
$collection->getSelect()->joinLeft(
array('orders_count' => new Zend_Db_Expr('(' . $select . ')')),
'main_table.entity_id = orders_count.customer_id',
array('orders_count.orders_count', 'orders_count.orders_total','COALESCE(orders_count,0) AS orders_count_cust')
);
echo $collection->getSelect()->__toString()
}
return $collection;
}
}
Normal sort by order working fine, but when i use the filter it give the error.
The Raw Sql (When total orders = 4):
SELECT `main_table`.*, `orders_count`.`orders_count`, `orders_count`.`orders_total`, COALESCE(orders_count,0) AS `orders_count_cust` FROM `customer_grid_flat` AS `main_table`
LEFT JOIN (SELECT `sales_order_grid`.*, CEIL(SUM(grand_total)) AS `orders_total`, COUNT(*) AS `orders_count` FROM `sales_order_grid` WHERE (status!='') GROUP BY `customer_id`) AS `orders_count` ON main_table.entity_id = orders_count.customer_id WHERE (`orders_count_cust` LIKE '%4%')
Error:
#1054 - Unknown column 'orders_count_cust' in 'where clause'
How can I fix that, thanks
