0

I´m trying to create a new calculated colunm on sales order grid. This column must indicate if all the products in the order are in stock at the moment the grid is displayed.

I managed to create the column overriding magento/vendor/magento/module-sales/view/adminhtml/ui_component/sales_order_grid.xml in my custom module and creating a listing column component in magento/app/code/[VENDOR]/[MODULE]/Ui/component/Listing/Column/Outofstock.php.

The column displays and its value seems to be correct, but when I try to order or filter the grid by this column, the magento query fails because the column is not in the database (Zend_Db_Statement_Exception(code: 42): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Outofstock' in 'order clause', query was: SELECT main_table.* FROM sales_order_grid AS main_table ORDER BY entity_id, Outofstock ASC).

How can I sort/filter by this rendered column without adding it to the database table?

Thanks in advance.

Below I put the contents of my main files:

magento/app/code/[VENDOR]/[MODULE]/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="Outofstock" class="[VENDOR]\[MODULE]\Ui\Component\Listing\Column\Outofstock">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="label" xsi:type="string" translate="true">Falta stock</item>
                    <item name="sortable" xsi:type="boolean">true</item>
                    <item name="filter" xsi:type="string">text</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

magento/app/code/[VENDOR]/[MODULE]/Ui/component/Listing/Column/Outofstock.php

<?php
namespace [VENDOR]\[MODULE]\Ui\Component\Listing\Column;

use Magento\CatalogInventory\Api\StockRegistryInterface;
use Magento\Framework\Api\SearchCriteriaBuilder;
use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Sales\Api\OrderRepositoryInterface;

use Magento\Ui\Component\Listing\Columns\Column;

class Outofstock extends Column
{
    protected $_orderRepository;
    protected $_searchCriteria;

    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        OrderRepositoryInterface $orderRepository,
        SearchCriteriaBuilder $criteria,
        array $components = [],
        array $data = [],
        StockRegistryInterface $stockRepository
    ) {
        $this->_orderRepository = $orderRepository;
        $this->_searchCriteria  = $criteria;

        $this->_stockRepository = $stockRepository;

        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    public function prepareDataSource(array $dataSource)
    {
        $logger=\Magento\Framework\App\ObjectManager::getInstance()->get(\Psr\Log\LoggerInterface::class);

        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                $order  = $this->_orderRepository->get($item["entity_id"]);
                //$logger->debug(basename(__FILE__) . '::' . __LINE__ . '::' . $order->getIncrementId());
                $orderItems = $order->getAllItems();//getAllVisibleItems();
                $algunoOutOfStock=false;
                foreach ($orderItems as $orderItem) {
                    //$logger->debug("    " . basename(__FILE__) . '::' . __LINE__ . '::' . $orderItem->getProductId());

                    $stockItem = $this->_stockRepository->getStockItem($orderItem->getProductId());
                    if ($stockItem->getData('is_in_stock') != 1) {
                        $algunoOutOfStock=true;
                    }
                }

                $item[$this->getData('name')] = ($algunoOutOfStock) ? 'SI' : 'NO';
            }
        }

        return $dataSource;
    }
}

1
  • Anybody can help?, please, I really need a solution – neslonso Oct 9 '20 at 8:23

Your Answer

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

Browse other questions tagged or ask your own question.