Magento 2 Add Store View Switcher Filter in UI Component Admin Grid

Question

I have created one grid that needs to apply the store view Switcher Filter it was displayed but when I click on apply filter it throws the below error.

Integrity constraint violation: 1052 Column 'store_id' in where clause is ambiguous.

This Error comes because the store_id column is available on so many tables so how to solve this apply filter for grid.

Below is my code.

<column name="store_id" class="Vendor\Extension\Ui\Component\Listing\Column\Store">
        <argument name="data" xsi:type="array">
            <item name="options" xsi:type="object">Magento\Cms\Ui\Component\Listing\Column\Cms\Options</item>
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">select</item>
                <item name="dataType" xsi:type="string">select</item>
            <item name="component" xsi:type="string">Vendor_Extension/js/ui/grid/columns/html</item>
            <item name="align" xsi:type="string">left</item>
            <item name="sortable" xsi:type="boolean">false</item>
            <item name="label" xsi:type="string" translate="true">Store View</item>
            </item>
        </argument>
    </column>

Answer 1

To resolve this issue, You need to specify from which table the store_id column should be used in your query.

Since you're working with a custom grid and have a custom column definition, you might need to customize the filtering process.

Based on the provided code, it looks like you're using a custom column class Vendor\Extension\Ui\Component\Listing\Column\Store for rendering the store view.

If this is the case, you might need to modify this custom column class to handle the filtering correctly.

Answer 2

Create a Custom Rendere:

 <?php

namespace Namespace\Module\Block\Adminhtml\Grid;

use Magento\Backend\Block\Widget\Grid\Column\Renderer\AbstractRenderer;
use Magento\Backend\Block\Context;
use Magento\Store\Model\StoreManagerInterface;

class StoreViewFilter extends AbstractRenderer
{
    protected $storeManager;

    public function __construct(
        Context $context,
        StoreManagerInterface $storeManager,
        array $data = []
    ) {
        parent::__construct($context, $data);
        $this->storeManager = $storeManager;
    }

    public function render(\Magento\Framework\DataObject $row)
    {
        $storeId = $row->getData($this->getColumn()->getIndex());
        $store = $this->storeManager->getStore($storeId);
        return $store->getName();
    }
}

Update the UI Component XM

<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
<dataSource name="your_grid_data_source">
    <!-- Define your data source configuration -->
</dataSource>
<columns name="your_columns">
    <!-- Define your columns configuration -->
    <column name="store_id" class="Namespace\Module\Block\Adminhtml\Grid\StoreViewFilter">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">text</item>
                <item name="sortable" xsi:type="boolean">false</item>
                <item name="label" xsi:type="string" translate="true">Store View</item>
            </item>
        </argument>
    </column>
</columns>