1

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')

2

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.

|improve this answer|||||
  • I used $colletion->addFilterToMap( 'customer_id', 'main_table.customer_id' ); without overriding and it works. Thanks for the code – tarek fellah Nov 21 '19 at 16:04
0

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;
}
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.