Create Admin grid from join table collection

Question

I'm new to magento 2, I have a query to join multiple table, only 1 custom table I've made and the rest is from Magento 2, the query looks like this:

SELECT 
    marketplace_orders.entity_id as id,
    marketplace_orders.product_ids as product_ids,
    sales_order.entity_id as order_id,
    sales_order.status as order_status,
    sales_order.state as order_state,
    customer_entity.entity_id as seller_id,
    CONCAT(customer_entity.firstname," ",customer_entity.lastname) as seller_name,
    sales_order_address.telephone as seller_telephone,
    customer_entity.email as seller_email
FROM marketplace_orders 
INNER JOIN sales_order
    ON sales_order.entity_id = marketplace_orders.order_id
       AND sales_order.status IN ("pending","processing")
LEFT JOIN customer_entity
    ON customer_entity.entity_id = marketplace_orders.seller_id
LEFT JOIN sales_order_address
    ON sales_order_address.parent_id = sales_order.entity_id

marketplace_orders structure:

order_id => foreign key from sales_order
seller_id => foreign key from customer_entity
product_ids => order item ids

This query will join the sales_order_item with the other table, and I need to show it in Magento 2 Admin grid, is there a way to produce the grid with that join table query?

Answer 1

You need follow steps to create admin grid in magento 2: https://www.mageplaza.com/magento-2-module-development/create-admin-grid-magento-2.html

Answer 2

follow step create admin grid and try, in namespace/module/etc/di.xml:

<virtualType name="namespace\module\Model\ResourceModel\ProductPerStore\ProductPerStore\Collection"
             type="namespace\module\Ui\Component\DataProvider\SearchResult\Items">
    <arguments>
        <argument name="mainTable" xsi:type="string">table_mysql</argument>
        <argument name="resourceModel" xsi:type="string">namespace\module\Model\ResourceModel\ProductPerStore\Collection</argument>
    </arguments>
</virtualType>

and in:

<?php
namespace namespace\module\Ui\Component\DataProvider\SearchResult;

class Items extends \Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult
{

    protected function _initSelect() {

        parent::_initSelect();
        $subquery = new \Zend_Db_Expr('(SELECT * from ...)');

        $this->getSelect()->join(array( 't' => $subquery ), 'main_table.entity_id = t.id', 
        array("*"))
        ->group("id")->order("id");
    }
}