How and where to add join query in magento 2 for UI components

Question

I have referred this link

Now where I have to place a code for join query multiple table for custom admin grid in magento 2

I have referred many links but got failed for ex.

adding below code in

Company\Module\Model\ResourceModel\Credits\collection.php

    protected function _initSelect()
        {
            echo 'hererer';
            die;
            parent::_initSelect();

            $this->getSelect()->joinLeft(
                    ['secondTable' => $this->getTable('customer_grid_flat')],
                    'main_table.customer_id = secondTable.entity_id',
                    array('*')
                );
            $this->addFilterToMap('firstname', 'secondTable.name');
            //$this->addFilterToMap('columnname2_clias', 'secondTable.columnname2');
            return $this;
        }


    <columns name="credit_listing_columns">
            <column name="customer_id">
                <settings>
                    <filter>text</filter>
                    <label translate="true">Customer ID</label>
                    <sorting>asc</sorting>
                </settings>
            </column>
            <column name="firstname">
                <settings>
                    <filter>text</filter>
                    <label translate="true">Customer Name</label>
                </settings>
            </column>
            <column name="credit">
...

what is the proper magento procedure or what is wrong with this, also I tried to echo die; inside this function but got failed.

Anshu Mishra · Accepted Answer · 2018-10-01T09:07:21.087

1

You can add the join query in ui component data source.
Code in module's di.xml

----
<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
            <arguments>
                <argument name="collections" xsi:type="array">
                    <item name="anshu_custom_listing_data_source" xsi:type="string">Anshu\Custom\Model\ResourceModel\ActiveSessions\Grid\Collection</item>
                </argument>
            </arguments>
        </type>
        <virtualType name="Anshu\Custom\Model\ResourceModel\ActiveSessions\Grid\Collection">
            <arguments>
                <argument name="mainTable" xsi:type="string">anshu_custom_table</argument>
                <argument name="resourceModel" xsi:type="string">Anshu\Custom\Model\ResourceModel\ActiveSessions</argument>
            </arguments>
        </virtualType>
----

Code in app/code/Anshu/Custom/Model/ResourceModel/ActiveSessions/Grid/Collection.php

----
protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(-----);

        return $this;
    }
----

Code in ui component xml file

-----
<dataProvider class="Anshu\Custom\Ui\Component\DataProvider" name="anshu_custom_listing_data_source">
----

Anshu\Custom\Model\ResourceModel\ActiveSessions\Grid\Collection is my ui component grid data source provider.

edited Oct 01 '18 at 09:07

answered Oct 01 '18 at 06:33

Anshu Mishra

8,950
7
40
88

thanks for your answer, anshu_custom_listing_data_source' this name is sequencial or any name we can give ? – SagarPPanchal Oct 01 '18 at 08:17
Name can be any string(it tried to have meaningful names, defining the purpose), but it should match the data source provider name in your ui component xml file. – Anshu Mishra Oct 01 '18 at 09:05
one last query, can we use order by clause inside _initSelect() ? – SagarPPanchal Oct 01 '18 at 09:52
Yes, we can use. I don't remember if I faced any issues with order by. Give it a try and share if it that works or if there is any issue. – Anshu Mishra Oct 01 '18 at 10:12
Yes, its working, using $this->setOrder('id','DESC'); :) thank you – SagarPPanchal Oct 01 '18 at 11:06

How and where to add join query in magento 2 for UI components

1 Answers1