Skip to content

Integrity constraint violation when filtering sales order grid by Purchasedate Magento2

I’ve implemented a custom plugin to display additional custom fields in the sales order grid and its values, and it is functioning as expected. However, when applying a filter on the ‘Purchase Date,’ I encounter the following error:

Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous at /var/www/html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:90.

I have already reviewed related discussions on GitHub(https://github.com/magento/magento2/issues/38818), specifically this issue, but would appreciate any additional guidance on resolving this error.”

di.xml

<type name="MagentoFrameworkViewElementUiComponentDataProviderCollectionFactory">
    <plugin name="showErpStatusInOrderGrid" type="VendorModulePluginFrameworkViewElementUiComponentDataProviderCollectionFactoryPlugin" sortOrder="1"/>
</type>

VendorModulePluginFrameworkViewElementUiComponentDataProviderCollectionFactoryPlugin

<?php

namespace VendorModulePluginFrameworkViewElementUiComponentDataProvider;

use MagentoFrameworkViewElementUiComponentDataProviderCollectionFactory;

class CollectionFactoryPlugin
{
    public function afterGetReport(
        CollectionFactory $subject,
        $collection,
        $requestName
    ) {
        if ($requestName == 'sales_order_grid_data_source') {
            $collection->join(
                ['sales_order' => 'sales_order'],
                'main_table.entity_id=sales_order.entity_id',
                ['shipping_slots','order_device','shipping_date','inventory_source','coupon_code']
            );
           
        }

        return $collection;
    }
}