0

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="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory"> <plugin name="showErpStatusInOrderGrid" type="Vendor\Module\Plugin\Framework\View\Element\UiComponent\DataProvider\CollectionFactoryPlugin" sortOrder="1"/> </type>

Vendor\Module\Plugin\Framework\View\Element\UiComponent\DataProvider\CollectionFactoryPlugin

<?php namespace Vendor\Module\Plugin\Framework\View\Element\UiComponent\DataProvider; use Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory; 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; } }
Improve this question

3 Answers 3

Reset to default
1

Try this approach

di.xml

<?xml version="1.0"?> <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd"> <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory"> <arguments> <argument name="collections" xsi:type="array"> <item name="sales_order_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\Collection</item> </argument> </arguments> </type> <type name="Vendor\Module\Model\ResourceModel\Order\Grid\Collection"> <arguments> <argument name="mainTable" xsi:type="string">sales_order_grid</argument> <argument name="resourceModel" xsi:type="string">Magento\Sales\Model\ResourceModel\Order</argument> </arguments> </type> </config>

Vendor\Module\Model\ResourceModel\Order\Grid\Collection.php

 protected function _renderFiltersBefore() { // place your JOIN here $joinTable = $this->getTable('sales_order'); $this->getSelect()->joinLeft( ['sales_order_table' => $joinTable], 'main_table.entity_id = sales_order_table.entity_id', ... parent::_renderFiltersBefore(); }

And give a look to addFilterToMap method, in the case you still get the ambiguous error

Improve this answer
0
0

The issue was resolved by implementing the following solution

Vendor/Module/etc/di.xml

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory"> <arguments> <argument name="collections" xsi:type="array"> <item name="sales_order_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\Collection</item> </argument> </arguments> </type> <type name="Vendor\Module\Model\ResourceModel\Order\Grid\Collection"> <arguments> <argument name="mainTable" xsi:type="string">sales_order_grid</argument> <argument name="resourceModel" xsi:type="string">Magento\Sales\Model\ResourceModel\Order</argument> </arguments> </type>

Vendor/Module/Model/ResourceModel/Order/Grid/Collection.php

<?php namespace Vendor\Module\Model\ResourceModel\Order\Grid; use Magento\Framework\Data\Collection\Db\FetchStrategyInterface as FetchStrategy; use Magento\Framework\Data\Collection\EntityFactoryInterface as EntityFactory; use Magento\Framework\Event\ManagerInterface as EventManager; use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as OriginalCollection; use Psr\Log\LoggerInterface as Logger; /** * Order grid extended collection */ class Collection extends OriginalCollection { /** * Constructor for initializing collection * * @param EntityFactory $entityFactory * @param Logger $logger * @param FetchStrategy $fetchStrategy * @param EventManager $eventManager * @param string $mainTable * @param string $resourceModel */ public function __construct( EntityFactory $entityFactory, Logger $logger, FetchStrategy $fetchStrategy, EventManager $eventManager, $mainTable = 'sales_order_grid', $resourceModel = \Magento\Sales\Model\ResourceModel\Order::class ) { parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $mainTable, $resourceModel); // Add custom filter mappings $this->addFilterToMap('order_device', 'so.order_device'); $this->addFilterToMap('shipping_slots', 'so.shipping_slots'); $this->addFilterToMap('shipping_date', 'so.shipping_date'); $this->addFilterToMap('coupon_code', 'so.coupon_code'); $this->addFilterToMap('inventory_source', 'so.inventory_source'); } /** * Render filters before query execution */ protected function _renderFiltersBefore() { $joinTable = $this->getTable('sales_order'); // Join the required columns from the sales_order table $this->getSelect()->joinLeft( ['so' => $joinTable], 'main_table.entity_id = so.entity_id', ['shipping_slots', 'order_device', 'shipping_date', 'inventory_source', 'coupon_code'] ); // Call parent method to apply any additional filtering parent::_renderFiltersBefore(); } }
Improve this answer
0

Try to use your code/modification with afterSearch plugin [ Class Magento\Framework\View\Element\UiComponent\DataProvider\Reporting ]

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.