Skip to content

Magento2: How to fix this Sales order grid created_at filter ambiguous error?

i needed custom column in sales Order grid so i applied joined on sales order with sales order grid but when I filter grid with purchase date It shows this error in logs and filter stop working.

Next Zend_Db_Statement_Exception: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT `main_table`.*, `sales_order`.`coupon_code`, `sales_order_address`.`telephone`, `sales_order_address`.`city`, `sales_order_address`.`country_id` FROM `sales_order_grid` AS `main_table`
 INNER JOIN `sales_order` ON main_table.entity_id = sales_order.entity_id
 INNER JOIN `sales_order_address` ON main_table.entity_id = sales_order_address.parent_id WHERE  (((`created_at` >= '2023-02-15 19:00:00'))) AND (((`created_at` <= '2023-02-16 18:59:59'))) AND (sales_order_address.address_type =  'shipping') ORDER BY main_table.created_at DESC
 LIMIT 20 in /var/www/html/magento245/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:109

and my join is this.

<?php

namespace VendorModuleModelResourceModelOrderGrid;

use MagentoFrameworkDataCollectionDbFetchStrategyInterface as FetchStrategy;
use MagentoFrameworkDataCollectionEntityFactoryInterface as EntityFactory;
use MagentoFrameworkEventManagerInterface as EventManager;
use MagentoSalesModelResourceModelOrder;
use MagentoSalesModelResourceModelOrderGridCollection as OriginalCollection;
use PsrLogLoggerInterface as Logger;

class Collection extends OriginalCollection
{
    public function __construct(
        EntityFactory $entityFactory,
        Logger $logger,
        FetchStrategy $fetchStrategy,
        EventManager $eventManager,
        $mainTable = 'sales_order_grid',
        $resourceModel = Order::class
    ) {
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $mainTable, $resourceModel);
    }

    protected function _initSelect()
    {
        $this->addFilterToMap('created_at', 'main_table.created_at');
        parent::_initSelect();
    }

    public function _renderFiltersBefore()
    {

        $joinTable = $this->getTable('sales_order_address');
        $this->getSelect()
            ->join($joinTable, 'main_table.entity_id = sales_order_address.parent_id',array('telephone','city','country_id') )->where("sales_order_address.address_type =  'shipping'");

        $salesOrderTable = $this->getTable('sales_order');
        $this->getSelect()->join($salesOrderTable, 'main_table.entity_id = sales_order.entity_id',['coupon_code']);

        parent::_renderFiltersBefore();
    }
}

Any Idea what I’m missing?