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

  • what you want actually still not getting you can you please explain – Rutvee Sojitra Sep 27 '18 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 – Manuel Di Iorio Sep 27 '18 at 12:36
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.