Filter a custom attribute in Sales Order Grid

Question

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

Answer 1

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