Order grid with advanced joins

Description

In order to add extra data to the sales_order_grid table, magento provide a way through di.xml extending the virtual type Magento\Sales\Model\ResourceModel\Order\Grid and adding columns or joins. In the joins case, there is no way to chage the main table. If the module has only one table to join with sales_order there is no problem, in case we have a multiple tables related is needed to add every primary key on the sales_order table.

Example

Table zones:

• entity_id
• name

Table prices:

• entity_id
• price

Table delivery_time:

• entity_id
• time

Table zones_matrix:

• entity_id
• zone_id
• delivery_id
• zipcode

Table sales_order:

• ...
• zone_matrix_id

Proposed solution

Change:

                    $this->mainTableName,

with:

                    $data['origin_table'] ?: $this->mainTableName,

This change will allow to use a custom table (that for sure will be joined before) to get others tables not related directly to sales_order.

https://github.com/magento/magento2/blob/28bedc7031ca0eca3d7b4e5830028fa934ed0ac4/app/code/Magento/Sales/Model/ResourceModel/Grid.php#L157

Read app/code/Magento/Sales/Model/ResourceModel/Grid.php at the linked line and review the di.xml join configuration described in the issue. Verify that origin_table can serve as the join source for related tables instead of sales_order, and confirm that the order grid can load the added data.