1

Following this answer: How to join order grid collection to custom table in Magento2? I'm able to join the order custom attribute and show it in the column, the error comes with the filter when applied:

INNER JOIN mgnt_sales_order AS so ON main_table.entity_id = so.entity_id WHERE (custom_attribute LIKE '%testing the filter%')

Integrity constraint violation: 1052 Column 'custom_attribute' in where clause is ambiguous

This is because the field is actually in the sales_order table and not sales_order_grid

view/adminhtml/ui_component/sales_order_grid.xml

<columns name="sales_order_columns"> <column name="custom_attribute"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="visible" xsi:type="boolean">true</item> <item name="filter" xsi:type="string">text</item> <item name="label" xsi:type="string" translate="true">Custom Attribute</item> </item> </argument> </column> </columns>

Plugins/AddColumnsSalesOrderGridCollection.php

<?php namespace Vendor\Module\Plugins; use Magento\Framework\Message\ManagerInterface as MessageManager; use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as SalesOrderGridCollection; class AddColumnsSalesOrderGridCollection { private $messageManager; private $collection; public function __construct(MessageManager $messageManager, SalesOrderGridCollection $collection ) { $this->messageManager = $messageManager; $this->collection = $collection; } public function aroundGetReport( \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject, \Closure $proceed, $requestName ) { $result = $proceed($requestName); $tableSalesOrder = $this->collection->getResource()->getTable('sales_order'); if ($requestName == 'sales_order_grid_data_source') { if ($result instanceof $this->collection) { $select = $this->collection->getSelect(); $select->join( ["so" => $tableSalesOrder], 'main_table.entity_id = so.entity_id', array('so.custom_attribute') ) ->distinct(); } } return $this->collection; } }

Goal: Filter the orders grid by a custom attribute of the order.

Unfortunally the solution with the plugin above creates other problems in the order view.. that's why I'm looking for other solutions

Improve this question
2
  • what you want actually still not getting you can you please explain Commented Sep 27, 2018 at 12:01
  • @RutveeSojitra being able to filter the orders grid by a custom attribute, which is joined from the sales_order table.. right now it shows the query error wrote above Commented Sep 27, 2018 at 12:36

1 Answer 1

Reset to default
2

Well.. seems like I found a workaround, very ugly. I persist the custom attribute in the sales_order_grid table when I retrieve the column value, through a raw PDO prepared statement query..

Ui/component/Listing/Column/CustomAttribute.php

<?php namespace Vendor\Module\Ui\Component\Listing\Column; use \Magento\Sales\Api\OrderRepositoryInterface; use \Magento\Framework\View\Element\UiComponent\ContextInterface; use \Magento\Framework\View\Element\UiComponentFactory; use \Magento\Ui\Component\Listing\Columns\Column; use \Magento\Framework\Api\SearchCriteriaBuilder; class CustomAttribute extends Column { protected $_orderRepository; protected $_searchCriteria; public function __construct( ContextInterface $context, UiComponentFactory $uiComponentFactory, OrderRepositoryInterface $orderRepository, SearchCriteriaBuilder $criteria, \Magento\Framework\App\ResourceConnection $resource, array $components = [], array $data = [] ) { $this->_orderRepository = $orderRepository; $this->_searchCriteria = $criteria; $this->resource = $resource; parent::__construct($context, $uiComponentFactory, $components, $data); $this->tableSalesOrderGrid = $this->resource->getTableName("sales_order_grid"); } public function prepareDataSource(array $dataSource) { if (isset($dataSource['data']['items'])) { $connection = $this->resource->getConnection(); foreach ($dataSource['data']['items'] as &$item) { $columnName = $this->getData('name'); $order = $this->_orderRepository->get($item["entity_id"]); $attrValue = $order->getData($columnName); $item[$columnName] = $attrValue; if ($columnName == "custom_attribute") { $sql = "UPDATE " . $this->tableSalesOrderGrid . " SET custom_attribute = :VALUE WHERE entity_id = " . $item["entity_id"]; $binds = [ "VALUE" => $attrValue ]; $connection->query($sql, $binds); } } } return $dataSource; } }

This class is specified in the ui_component/sales_order_grid.xml file

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.