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?