2

I have created a custom attribute "item location" for every product. It was created in Stores -> Attributes -> Product, then assigned to the default attribute set.

So every item has its own location in the warehouse which is shown in this attribute. I can see this attribute in Catalog -> Products grid, but I would like these data in Sales Orders grid as well. Any idea?

  • You can add by using renderer, but it will be complex if you want to search/sorting feature. – Sohel Rana May 11 at 6:18
  • Best would be if I could also sort by item location or search, but for now I would be happy if I could simply show a column "item location" with its data next to evry product SKU in the sales Orders grid. I have previously added to the Sales Orders grid column product name, but I have no idea how to get the data from custom attribute "item location" – Peter May 11 at 6:41
1

Try following way:

app/code/SR/MagentoCommunity/etc/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">
        <plugin name="join_sales_order_grid_with_product"
                type="SR\MagentoCommunity\Plugin\UiComponent\DataProvider\CollectionFactory" sortOrder="100" />
    </type>
</config>

app/code/SR/MagentoCommunity/Plugin/UiComponent/DataProvider/CollectionFactory.php

<?php
namespace SR\MagentoCommunity\Plugin\UiComponent\DataProvider;

use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as OrderGridCollection;

class CollectionFactory
{
    public function afterGetReport(
        \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
        $collection,
        $requestName
    ) {
        if ($requestName == 'sales_order_grid_data_source') {
            if ($collection instanceof OrderGridCollection) {
                $collection->getSelect()
                    ->join(
                        $collection->getTable('sales_order_item'),
                        'main_table.entity_id = '.$collection->getTable('sales_order_item').'.order_id',
                        [
                            'product_skus' => new \Zend_Db_Expr('GROUP_CONCAT(`sales_order_item`.sku SEPARATOR "|")'),
                            'product_names' => new \Zend_Db_Expr('GROUP_CONCAT(`sales_order_item`.name SEPARATOR "|")')
                        ]
                    )
                    ->where('parent_item_id IS NULL')
                    ->group('main_table.entity_id');
            }
        }

        return $collection;
    }
}

If you manage product sku in grid, then you can avoid above two steps.

Now how to display in grid:

app/code/SR/MagentoCommunity/view/adminhtml/ui_component/sales_order_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="item_location" class="SR\MagentoCommunity\Ui\Component\Listing\Column\ItemLocation">
            <settings>
                <filter>textRange</filter>
                <label translate="true">Item Location</label>
                <visible>true</visible>
                <filter>false</filter>
                <sortable>false</sortable>
            </settings>
        </column>
        <column name="product_names">
            <settings>
                <filter>text</filter>
                <label translate="true">Product Name</label>
                <visible>true</visible>
                <filter>false</filter>
                <sortable>false</sortable>
            </settings>
        </column>
    </columns>
</listing>

app/code/SR/MagentoCommunity/Ui/Component/Listing/Column/ItemLocation.php

<?php
namespace SR\MagentoCommunity\Ui\Component\Listing\Column;

use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Ui\Component\Listing\Columns\Column;
use Magento\Catalog\Model\ProductRepository;

class ItemLocation extends Column
{
    /**
     * @var ProductRepository
     */
    private $productRepository;

    /**
     * ItemLocation constructor.
     *
     * @param ContextInterface $context
     * @param UiComponentFactory $uiComponentFactory
     * @param ProductRepository $productRepository
     * @param array $components
     * @param array $data
     */
    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        ProductRepository $productRepository,
        array $components = [],
        array $data = []
    ) {
        $this->productRepository = $productRepository;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    /**
     * Prepare Data Source
     *
     * @param array $dataSource
     * @return array
     */
    public function prepareDataSource(array $dataSource)
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                $productSkus = isset($item['product_skus']) ? $item['product_skus'] : null;
                $itemLocation = [];
                if ($productSkus) {
                    $productSkus = explode('|', $productSkus);
                    foreach ($productSkus as $sku) {
                        try {
                            $product = $this->productRepository->get($sku);
                            if (trim($product->getAttributeText("country_of_manufacture"))) {
                                $itemLocation[] = $product->getAttributeText("country_of_manufacture");
                            }
                        } catch (\Exception $e) {}
                    }
                }

                if (count($itemLocation) <= 0) {
                    $itemLocation[] = 'N/A';
                }
                $item[$this->getData('name')] = implode('|', $itemLocation);
            }
        }

        return $dataSource;
    }
}

Here I use country_of_manufacture attribute as an example. This is dropdown. If your custom attribute is not dropdown then you can replace by following code:

if ($customAttribute = $product->getData('custom_attribute')) {
    $itemLocation[] = $customAttribute;
}

instead of

if (trim($product->getAttributeText("country_of_manufacture"))) {
    $itemLocation[] = $product->getAttributeText("country_of_manufacture");
}
  • I have created the module exactly as you have written.I am getting error on Sales Order Grid: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_order_item.sku' in 'field list', query was: SELECT main_table.*, GROUP_CONCAT(sales_order_item.sku SEPARATOR "|") AS product_skus, GROUP_CONCAT(sales_order_item.name SEPARATOR "|") AS product_names FROM mg_sales_order_grid AS main_table INNER JOIN mg_sales_order_item ON main_table.entity_id = mg_sales_order_item.order_id WHERE (parent_item_id IS NULL) GROUP BY main_table.entity_id LIMIT 20 – Peter May 11 at 16:50
  • This error means, plugin not working in your case. Did you created new module? or just created my file? – Sohel Rana May 11 at 16:57
  • Yes, I have created module SR/MagentoCommunity with all the files you provided – Peter May 11 at 17:04
  • Did I do anything wrong? – Peter May 11 at 17:14
  • Did you use table prefix? sales_order_item will replace by mg_sales_order_item. – Sohel Rana May 11 at 17:41

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.