Magento2: How to use Custom Collection Data in UI Component Grid

Question

I am creating a custom module to show my custom records on grid (using UI component),till now I have Created UI Component, but unable to link up with the Collection. Here is my UI component code:

<?xml version="1.0" ?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <argument name="context" xsi:type="configurableObject">
        <argument name="class" xsi:type="string">Magento\Framework\View\Element\UiComponent\Context</argument>
        <argument name="namespace" xsi:type="string">test_productwise_index</argument>
    </argument>
    <argument name="data" xsi:type="array">
        <item name="js_config" xsi:type="array">
            <item name="provider" xsi:type="string">test_productwise_index.test_productwise_grid_data_source</item>
            <item name="deps" xsi:type="string">test_productwise_index.test_productwise_grid_data_source</item>
        </item>
        <item name="spinner" xsi:type="string">test_productwise_columns</item>
    </argument>
    <listingToolbar name="listing_top">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="sticky" xsi:type="boolean">false</item>
            </item>
        </argument>
        <bookmark name="bookmark"/>
        <columnsControls name="columns_controls"/>
        <filters name="listing_filters"/>
        <paging name="listing_paging"/>
    </listingToolbar>
    <columns name="test_productwise_columns">

        <column name="sku">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="sortOrder" xsi:type="number">10</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="sorting" xsi:type="string">asc</item>
                    <item name="label" translate="true" xsi:type="string">Sku</item>
                </item>
            </argument>
        </column>
        <column name="name">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="sortOrder" xsi:type="number">20</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Name</item>
                    <item name="editor" xsi:type="array">
                        <item name="editorType" xsi:type="string">text</item>
                        <item name="validation" xsi:type="array">
                            <item name="required-entry" xsi:type="boolean">false</item>
                        </item>
                    </item>
                </item>
            </argument>
        </column>
        <column name="customer_email">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="sortOrder" xsi:type="number">30</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Email</item>
                    <item name="editor" xsi:type="array">
                        <item name="editorType" xsi:type="string">text</item>
                        <item name="validation" xsi:type="array">
                            <item name="required-entry" xsi:type="boolean">false</item>
                        </item>
                    </item>
                </item>
            </argument>
        </column>
        <column name="sales_order_created_at">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="sortOrder" xsi:type="number">40</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Created At</item>
                    <item name="editor" xsi:type="array">
                        <item name="editorType" xsi:type="string">text</item>
                        <item name="validation" xsi:type="array">
                            <item name="required-entry" xsi:type="boolean">false</item>
                        </item>
                    </item>
                </item>
            </argument>
        </column>
        <column name="qty_ordered">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="sortOrder" xsi:type="number">50</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Ordered</item>
                    <item name="editor" xsi:type="array">
                        <item name="editorType" xsi:type="string">text</item>
                        <item name="validation" xsi:type="array">
                            <item name="required-entry" xsi:type="boolean">false</item>
                        </item>
                    </item>
                </item>
            </argument>
        </column>

    </columns>
</listing>

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="test_productwise_grid_data_source" xsi:type="string">Test\Productwise\Model\ResourceModel\Productwise\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
    <virtualType name="Test\Productwise\Model\ResourceModel\Productwise\Grid\Collection" type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
        <arguments>
            <argument name="resourceModel" xsi:type="string">Test\Productwise\Model\ResourceModel\Productwise\Collection</argument>
        </arguments>
    </virtualType>
    <type name="Test\Productwise\Block\Index\Index">
        <arguments>
            <argument name="templateProcessor" xsi:type="object">Magento\Widget\Model\Template\Filter</argument>
        </arguments>
    </type>
</config>

I am passing sql query in collection:

namespace Test\Productwise\Model\ResourceModel\Testwise;

class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection
{
protected function _initSelect()
{
    parent::_initSelect();

    $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
    $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
    $connection = $resource->getConnection();
    $sales_order = $resource->getTableName('sales_order'); 
    $sales_order_item = $resource->getTableName('sales_order_item'); 
    $sql ="SELECT `main_table`.*, `sales_order`.`created_at` AS `sales_order_created_at`, `sales_order`.`entity_id`, `sales_order`.`increment_id`, `sales_order`.`store_id`, `sales_order`.`subtotal`, `sales_order`.`customer_id`, `qty_ordered` AS `qtytotal` FROM $sales_order_item AS `main_table` LEFT JOIN $sales_order AS `sales_order` ON `main_table`.order_id = `sales_order`.entity_id"; 
    $result = $connection->fetchAll($sql); 

    return $result;

}
}

I am new to magento 2,like in magento 1 we use prepare_collection and get the collection whether its from db or from custom query, here in magento 2 how can we do that. Thanks

Answer 1

Your UI component has no datasource component like the one indicated here https://devdocs.magento.com/guides/v2.3/ui_comp_guide/concepts/ui_comp_data_source.html#declaring-the-xml

Try adding datasource component in your UI code. It should look like this

<dataSource name="test_productwise_grid_data_source">
        <argument name="dataProvider" xsi:type="configurableObject">
            <argument name="class" xsi:type="string">Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider</argument>
            <argument name="name" xsi:type="string">test_productwise_grid_data_source</argument>
            <argument name="primaryFieldName" xsi:type="string">id</argument>
            <argument name="requestFieldName" xsi:type="string">id</argument>
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="update_url" xsi:type="url" path="mui/index/render"/>
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/provider</item>
                </item>
            </argument>
        </argument>
</dataSource>