Add column to order grid get error in customer page

Question

I tried to add 2 new columns (shipping's company and billing's company) to orders page using a plugin, but i got an error when trying to view customer's orders in customer page. enter image description here This is my plugin function

public function afterGetReport($subject, $collection, $requestName)
{
 if ($requestName !== 'sales_order_grid_data_source') {
 return $collection;
 }
 if ($collection->getMainTable() === $collection->getResource()->getTable('sales_order_grid')) {
 try {
 $orderAddressTableName = $collection->getResource()->getTable('sales_order_address');
 //$directoryCountryRegionTableName = $collection->getResource()->getTable('directory_country_region');
 $collection->getSelect()->joinLeft(
 ['soat' => $orderAddressTableName],
 'soat.parent_id = main_table.entity_id AND soat.address_type = \'shipping\'' ,
 [
 'company_shipp' => 'company'
 ]
 );
 $collection->getSelect()->joinLeft(
 ['soat1' => $orderAddressTableName],
 'soat1.parent_id = main_table.entity_id AND soat1.address_type = \'billing\'',
 [
 'company_bill' => 'company'
 ]
 )->where();
 } catch (\Zend_Db_Select_Exception $selectException) {
 // Do nothing in that case
 $this->logger->log(100, $selectException);
 }
 }
 return $collection;
 }

My be my code is wrong to get both billing and shpping company in 2 custom columns, But i got error This is the error Column 'customer_id' in where clause is ambiguous, can i override the where in my function but how to get the customer?

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'customer_id' in where clause is ambiguous, query was: SELECT COUNT(*) FROM sales_order_grid AS main_table LEFT JOIN ggg_flags_order_flag AS amflags_column_1 ON main_table.entity_id = amflags_column_1.order_id AND amflags_column_1.column_id = 1 LEFT JOIN ggg_flags_order_flag AS amflags_column_2 ON main_table.entity_id = amflags_column_2.order_id AND amflags_column_2.column_id = 2 LEFT JOIN ggg_flags_order_flag AS amflags_column_3 ON main_table.entity_id = amflags_column_3.order_id AND amflags_column_3.column_id = 3 LEFT JOIN ggg_flags_order_flag AS amflags_column_4 ON main_table.entity_id = amflags_column_4.order_id AND amflags_column_4.column_id = 4 LEFT JOIN ggg_flags_order_flag AS amflags_column_5 ON main_table.entity_id = amflags_column_5.order_id AND amflags_column_5.column_id = 5 LEFT JOIN ggg_flags_order_flag AS amflags_column_6 ON main_table.entity_id = amflags_column_6.order_id AND amflags_column_6.column_id = 6 LEFT JOIN ggg_flags_order_flag AS amflags_column_7 ON main_table.entity_id = amflags_column_7.order_id AND amflags_column_7.column_id = 7 LEFT JOIN ggg_flags_order_flag AS amflags_column_8 ON main_table.entity_id = amflags_column_8.order_id AND amflags_column_8.column_id = 8 LEFT JOIN ggg_flags_order_flag AS amflags_column_9 ON main_table.entity_id = amflags_column_9.order_id AND amflags_column_9.column_id = 9 LEFT JOIN ggg_flags_order_flag AS amflags_column_10 ON main_table.entity_id = amflags_column_10.order_id AND amflags_column_10.column_id = 10 LEFT JOIN ggg_flags_order_flag AS amflags_column_11 ON main_table.entity_id = amflags_column_11.order_id AND amflags_column_11.column_id = 11 LEFT JOIN sales_order_address AS soat ON soat.parent_id = main_table.entity_id LEFT JOIN sales_order_address AS soat1 ON soat1.parent_id = main_table.entity_id WHERE (soat.address_type = 'shipping') AND (soat1.address_type = 'billing') AND (**customer_id** = '11617')

Answer 1

Overwrite following class:

Magento\Sales\Model\ResourceModel\Order\Grid\Collection

After that add following method into your class:

/**
 * {@inheritdoc}
 */
protected function _construct()
{
 parent::_construct();
 $this->addFilterToMap(
 'customer_id',
 'main_table.customer_id'
 );
}

Note: this is not tested code, but that way should work.

• tarek fellah: I used $colletion->addFilterToMap( 'customer_id', 'main_table.customer_id' ); without overriding and it works. Thanks for the code

Answer 2

With code given by @Sohel, i changed field prefix without overriding any class, it contains code to fix filtering issue for the new columns

public function afterGetReport($subject, $collection, $requestName)
{
 if ($requestName !== 'sales_order_grid_data_source') {
 return $collection;
 }
 if ($collection->getMainTable() === $collection->getResource()->getTable('sales_order_grid')) {
 try {
 $orderAddressTableName = $collection->getResource()->getTable('sales_order_address');
 //$directoryCountryRegionTableName = $collection->getResource()->getTable('directory_country_region');
 $collection->getSelect()->joinLeft(
 ['soat' => $orderAddressTableName],
 'soat.parent_id = main_table.entity_id AND( soat.address_type = \'shipping\')' ,
 [
 'company_shipp' => 'company'
 ]
 );
 $collection->getSelect()->joinLeft(
 ['soat1' => $orderAddressTableName],
 'soat1.parent_id = main_table.entity_id AND soat1.address_type = \'billing\'',
 [
 'company_bill' => 'company'
 ]
 );
 $collection
 ->addFilterToMap(
 'company_bill',
 'soat1.company'
 );
 $collection
 ->addFilterToMap(
 'company_shipp',
 'soat.company'
 );
 $collection->addFilterToMap(
 'customer_id',
 'main_table.customer_id'
 );
 } catch (\Zend_Db_Select_Exception $selectException) {
 // Do nothing in that case
 var_dump($collection);
 $this->logger->log(100, $selectException);
 }
 }
 return $collection;
}