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:

    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?

2 Answers 2


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


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

<virtualType name="namespace\module\Model\ResourceModel\ProductPerStore\ProductPerStore\Collection"
        <argument name="mainTable" xsi:type="string">table_mysql</argument>
        <argument name="resourceModel" xsi:type="string">namespace\module\Model\ResourceModel\ProductPerStore\Collection</argument>

and in:

namespace namespace\module\Ui\Component\DataProvider\SearchResult;

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

    protected function _initSelect() {

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

        $this->getSelect()->join(array( 't' => $subquery ), 'main_table.entity_id = t.id', 

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.