I have a custom table that is assigned to multiple store so i do have another table for their relationship. my problem now is i want to filter my custom table by store in the admin grid. i was able to display it by manipulating my data provider. but my filter is not working because mui/index/render is trying filtering store_id in the main_table which does not exist.

i getting an error in the exeption.log "Unknown column 'store_id' in 'where clause', query was: SELECT COUNT(*) FROM my_table AS main_table WHERE (store_id = '1')"


 public function __construct(
    \My\Module\Model\ResourceModel\faqs\Collection $faqsCollection,
    \Magento\Framework\App\RequestInterface $request,
    \My\Module\Helper\Resource $resourceHelper,
    array $meta = [],
    array $data = []
    $this->collection = $faqsCollection;
    $this->request = $request;
    $this->resourceHelper = $resourceHelper;
    parent::__construct($name, $primaryFieldName, $requestFieldName, $meta, $data);

public function getData()
    if (isset($this->loadedData)) {
        return $this->loadedData;

    $faqsId = $this->request->getParam('faqs_id');
    if($faqsId){ //form data. please dont mind this part
        $faqsCollection = $this->collection->addFieldToFilter('faqs_id', $faqsId);
        foreach ($faqsCollection as $faqs){
            $this->loadedData[$faqs->getId()] = $faqs->getData();
    } else { //grid data. here is where i manipulate the data for the grid
        $faqsCollection = $this->collection->toArray();
        $this->loadedData['totalRecords'] = $faqsCollection['totalRecords'];
        foreach ($faqsCollection['items'] as $faqs){
            //this returns an array of store from the relationship table [1,2,3,4]
            $faqs['store_id'] = $this->resourceHelper->getTopicStores($faqs['topic_id']);
            $this->loadedData['items'][] = $faqs;

    return $this->loadedData;

then in my faqs_grid.xml ui_component

<dataSource name="faqs_grid_data_source" component="Magento_Ui/js/grid/provider">
            <param name="indexField" xsi:type="string">faqs_id</param>
        <updateUrl path="mui/index/render"/>
    <dataProvider class="My\Module\Model\faqs\DataProvider" name="faqs_grid_data_source">

<listingToolbar name="listing_top">
    <argument name="data" xsi:type="array">
        <item name="config" xsi:type="array">
            <item name="sticky" xsi:type="boolean">true</item>
    <columnsControls name="columns_controls"/>
    <filters name="listing_filters">
        <argument name="data" xsi:type="array">
            <item name="observers" xsi:type="array">
                <item name="column" xsi:type="string">column</item>
                        <param name="template" xsi:type="string">ui/grid/filters/elements/ui-select</param>
                        <param name="component" xsi:type="string">Magento_Ui/js/form/element/ui-select</param>
        <filterSelect name="store_id" provider="${ $.parentName }">
                <options class="Magento\Cms\Ui\Component\Listing\Column\Cms\Options"/>
                <label translate="true">Store View</label>
                    <link name="visible">componentType = column, index = ${ $.index }:visible</link>
    <paging name="listing_paging"/>

<columns name="faq_listing_columns">
    <column name="store_id" class="Magento\Store\Ui\Component\Listing\Column\Store">
            <label translate="true">Store View</label>

the displaying is working properly. but i need to find a way to filter it by store_id.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.