1

I'm trying to filter orders by store instead of store view. I have 2 stores, each has 2 store views. I wanted to filter order with store.

I declared a collection in di.xml

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
    <arguments>
        <argument name="collections" xsi:type="array">
            <item name="sales_order_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\Collection</item>
        </argument>
    </arguments>
</type>

After that, in collection.php:

protected function _renderFiltersBefore()
{
    $joinTable1 = $this->getTable('sales_order');
    $this->getSelect()->joinLeft($joinTable1, 'main_table.entity_id = sales_order.entity_id ', ['store_id']);
    parent::_renderFiltersBefore();
}

Im not sure, how to proceed

3 Answers 3

2
+50

You should join the store table to the sales_order_grid table to add a store id (group id) to it:

app/code/MageWorx/FilterByStore/etc/adminhtml/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">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="sales_order_grid_data_source" xsi:type="string">MageWorx\FilterByStore\Model\ResourceModel\Order\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
</config>

MageWorx\FilterByStore\Model\ResourceModel\Order\Grid\Collection

<?php
/**
 * Copyright © MageWorx. All rights reserved.
 * See LICENSE.txt for license details.
 */
declare(strict_types=1);

namespace MageWorx\FilterByStore\Model\ResourceModel\Order\Grid;

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

class Collection extends OriginalCollection
{
    /**
     * Add the store column to filters map.
     */
    protected function _initSelect()
    {
        parent::_initSelect();

        $this->addFilterToMap('store', 'store_table.group_id');

        return $this;
    }

    /**
     * Join store table to the main table. Now all group ids will be available in the grid.
     * 
     * @return void
     */
    protected function _renderFiltersBefore()
    {
        $storeTable = $this->getTable('store');
        $this->getSelect()->joinLeft(
            ['store_table' => $storeTable],                         // store_table is an alias
            'main_table.store_id = store_table.store_id ',    // join using store_id (store view id)
            ['store' => 'group_id']                                 // store is an alias
        );
        parent::_renderFiltersBefore();
    }
}

Now our grid has all the necessary data. A new filter has become available but has not been defined yet. The store column will be invisible because we didn't define it in <columns> section of UI listing.

We must define filter:

app/code/MageWorx/FilterByStore/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:framework:Ui/etc/ui_configuration.xsd">
    <dataSource name="sales_order_grid_data_source">
        <argument name="dataProvider" xsi:type="configurableObject">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="storageConfig" xsi:type="array">
                        <item name="indexField" xsi:type="string">entity_id</item>
                    </item>
                </item>
            </argument>
        </argument>
    </dataSource>
    <listingToolbar name="listing_top">
        <filters name="listing_filters">
            <filterSelect name="store" class="Magento\Ui\Component\Filters\Type\Select" provider="${ $.parentName }">
                <settings>
                    <options class="MageWorx\FilterByStore\Ui\Component\Listing\Column\Store\Options"/>
                    <caption translate="true">All Stores</caption>
                    <label translate="true">Stores</label>
                    <dataScope>store</dataScope>
                </settings>
            </filterSelect>
        </filters>
    </listingToolbar>
</listing>

Since we have not defined our column and its filter type, we must define it directly in the filter, and it must have the Magento\Ui\Component\Filters\Type\Select type. <dataScope>store</dataScope> is our column alias (name) in the table. For this filter we need to create an options provider that should display the labels of all available stores:

MageWorx\FilterByStore\Ui\Component\Listing\Column\Store\Options

<?php
declare(strict_types=1);

namespace MageWorx\FilterByStore\Ui\Component\Listing\Column\Store;

use Magento\Framework\Escaper;
use Magento\Framework\Data\OptionSourceInterface;
use Magento\Store\Model\System\Store as SystemStore;

/**
 * UI store options (not store views!)
 */
class Options implements OptionSourceInterface
{
    /**
     * Escaper
     *
     * @var Escaper
     */
    protected $escaper;

    /**
     * System store
     *
     * @var SystemStore
     */
    protected $systemStore;

    /**
     * @var array
     */
    protected $options;

    /**
     * @var array
     */
    protected $currentOptions = [];

    /**
     * Constructor
     *
     * @param SystemStore $systemStore
     * @param Escaper $escaper
     */
    public function __construct(SystemStore $systemStore, Escaper $escaper)
    {
        $this->systemStore = $systemStore;
        $this->escaper     = $escaper;
    }

    /**
     * Get options
     *
     * @return array
     */
    public function toOptionArray()
    {
        if ($this->options !== null) {
            return $this->options;
        }

        $this->generateCurrentOptions();

        $this->options = array_values($this->currentOptions);

        return $this->options;
    }

    /**
     * Sanitize website/store option name
     *
     * @param string $name
     *
     * @return string
     */
    protected function sanitizeName($name)
    {
        $matches = [];
        preg_match('/\$[:]*{(.)*}/', $name, $matches);
        if (count($matches) > 0) {
            $name = $this->escaper->escapeHtml($this->escaper->escapeJs($name));
        } else {
            $name = $this->escaper->escapeHtml($name);
        }

        return $name;
    }

    /**
     * Generate current options
     *
     * @return void
     */
    protected function generateCurrentOptions(): void
    {
        $websiteCollection = $this->systemStore->getWebsiteCollection();
        $groupCollection   = $this->systemStore->getGroupCollection();

        foreach ($websiteCollection as $website) {
            foreach ($groupCollection as $group) {
                if ($group->getWebsiteId() === $website->getId()) {
                    $stores[] = [
                        'label' => str_repeat(' ', 4) . $this->sanitizeName($group->getName()),
                        'value' => $group->getId(),
                    ];
                }
            }

            if (!empty($stores)) {
                $this->currentOptions[] = [
                    'label' => $this->sanitizeName($website->getName()),
                    'value' => array_values($stores),
                ];
            }
        }
    }
}

Now, after refresh the page we will se something similar to this:

result filter 1 result filter 2

For the test I have create a new store, with one order. Total order number is 930 (929 in the Main Store and 1 in Store #2).

Without filter, total 930:

test without filter

Filter was set to Main Store, total orders 929:

test with main store filter

Filter was set to Store #2, total orders 1:

Test with store #2 fitler

Result with other filter active and with custom sort by:

custom filter + custom sort by


Example module is available on GitHub.


PS: the Victor's answer is also great solution. You can use it too.

2
  • FYI: This example is more complicated and has some cons like extra join and overwrite data grid provider. You might have the issue when you need to extend the same data grid provider in few extensions or some 3rd party code already overwrote it. Jun 7 at 14:13
  • @VictorTihonchuk sure, but it's based on the source code of question, where the data provider was changed to the custom collection. Because of that I noticed in the end of my answer that your's solution is "also great". Jun 7 at 14:19
1

Example how to add filter by store (group) in orders grid

app/code/Acme/StackExchange/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">
    <listingToolbar name="listing_top">
        <filters name="listing_filters">
            <filterSelect name="store_group_id" class="Acme\StackExchange\Ui\Component\Listing\Filter\StoreGroup" provider="${ $.parentName }">
                <settings>
                    <options class="Acme\StackExchange\Ui\Component\Listing\Column\StoreGroup"/>
                    <caption translate="true">All Stores</caption>
                    <label translate="true">Purchase Store</label>
                    <dataScope>store_group_id</dataScope>
                    <imports>
                        <link name="visible">ns = ${ $.ns }, index = ${ $.index }:visible</link>
                    </imports>
                </settings>
            </filterSelect>
        </filters>
    </listingToolbar>
</listing>

app/code/Acme/StackExchange/Ui/Component/Listing/Column/StoreGroup.php

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Ui\Component\Listing\Column;

use Magento\Framework\Data\OptionSourceInterface;
use Magento\Framework\Escaper;
use Magento\Store\Model\Group;
use Magento\Store\Model\System\Store as SystemStore;
use Magento\Store\Model\Website;

class StoreGroup implements OptionSourceInterface
{
    /**
     * @var SystemStore
     */
    protected $systemStore;

    /**
     * @var Escaper
     */
    protected $escaper;

    /**
     * @var array
     */
    protected $options;

    public function __construct(
        SystemStore $systemStore,
        Escaper $escaper
    ) {
        $this->systemStore = $systemStore;
        $this->escaper     = $escaper;
    }

    /**
     * @inheritDoc
     */
    public function toOptionArray(): array
    {
        if ($this->options === null) {
            $this->options = $this->getOptions();
        }

        return $this->options;
    }

    /**
     * Sanitize website/store option name
     *
     * @param string $name
     * @return string
     */
    protected function sanitizeName(string $name): string
    {
        $matches = [];
        preg_match('/\$[:]*{(.)*}/', $name, $matches);
        if (count($matches) > 0) {
            $name = $this->escaper->escapeHtml($this->escaper->escapeJs($name));
        } else {
            $name = $this->escaper->escapeHtml($name);
        }

        return $name;
    }

    protected function getOptions(): array
    {
        $options = [];

        /** @var Website $website */
        foreach ($this->systemStore->getWebsiteCollection() as $website) {
            $groups = [];
            /** @var Group $group */
            foreach ($this->systemStore->getGroupCollection() as $group) {
                if ($group->getWebsiteId() === $website->getId()) {
                    $stores = 0;
                    foreach ($this->systemStore->getStoreCollection() as $store) {
                        if ($store->getGroupId() === $group->getId()) {
                            $stores++;
                        }
                    }
                    if ($stores > 0) {
                        $groups[] = [
                            'label' => str_repeat(' ', 4) . $this->sanitizeName($group->getName()),
                            'value' => $group->getId(),
                        ];
                    }
                }
            }
            if (!empty($groups)) {
                $options[] = [
                    'label' => $this->sanitizeName($website->getName()),
                    'value' => array_values($groups),
                ];
            }
        }

        return $options;
    }
}

app/code/Acme/StackExchange/Ui/Component/Listing/Filter/StoreGroup.php

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Ui\Component\Listing\Filter;

use Magento\Framework\Api\FilterBuilder;
use Magento\Framework\Data\OptionSourceInterface;
use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Store\Model\Store;
use Magento\Store\Model\System\Store as SystemStore;
use Magento\Ui\Component\Filters\FilterModifier;
use Magento\Ui\Component\Filters\Type\Select;

class StoreGroup extends Select
{
    /**
     * @var SystemStore
     */
    protected $systemStore;

    /**
     * @inheritDoc
     */
    public function __construct(
        ContextInterface $context,
        SystemStore $systemStore,
        UiComponentFactory $uiComponentFactory,
        FilterBuilder $filterBuilder,
        FilterModifier $filterModifier,
        OptionSourceInterface $optionsProvider = null,
        array $components = [],
        array $data = []
    ) {
        $this->systemStore = $systemStore;

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

    /**
     * @inheritDoc
     */
    protected function applyFilter()
    {
        if (isset($this->filterData[$this->getName()])) {
            $value = $this->filterData[$this->getName()];

            $storeIds = [];
            /** @var Store $store */
            foreach ($this->systemStore->getStoreCollection() as $store) {
                if ($store->getGroupId() == $value) {
                    $storeIds[] = $store->getId();
                }
            }

            $filter = $this->filterBuilder->setConditionType('eq')
                ->setField('store_id')
                ->setValue($storeIds)
                ->create();

            $this->getContext()->getDataProvider()->addFilter($filter);
        }
    }
}

Result

Filters

Applied filter

0

Use this Query

$this->collection->getSelect()->joinLeft(
            ['sales' => $this->collection->getResource()->getTable('sales_order')],
            'main_table.entity_id = sales.entity_id',
            []
        )->where('sales.store_id IS NULL OR sales.store_id = ' . $storeId);

$this->collection is your main table collection.

$storeId is your store_id

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.