Extension Attributes Join Directive is not optimized for MySQL left join

[swnsma]

Summary (*)

When you use multiple extension attributes from the same table, left join will be generated for each case.
Issue has been faced during 'In Store Pickup' feature implementation in PR: magento/inventory#2125.

Examples (*)

1. Create simple table via db_schema.xml

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"       xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="custom_table" engine="innodb">
        <column xsi:type="int" name="order_id"  padding="10" unsigned="true" nullable="false"/>
        <column xsi:type="varchar" name="custom_col1" nullable="false" default="custom_value1"/>
        <column xsi:type="varchar" name="custom_col2" nullable="false" default="custom_value2"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="order_id"/>
        </constraint>
        <constraint xsi:type="foreign" referenceId="CUSTOM_TABLE_ORDER_ID_SALES_ORDER_ENTITY_ID"
                table="custom_table" column="order_id" referenceTable="sales_order"
                referenceColumn="entity_id" onDelete="CASCADE"/>
    </table>
</schema>

And db_schema_whitelist.json

{
    "custom_table": {
        "column": {
            "order_id": true,
            "custom_col1": true,
            "custom_col2": true
        },
        "constraint": {
            "PRIMARY": true,
            "CUSTOM_TABLE_ORDER_ID_SALES_ORDER_ENTITY_ID": true
        }
    }
}

2. Create extension attribute in extension_attributes.xml

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Api/etc/extension_attributes.xsd">
    <extension_attributes for="Magento\Sales\Api\Data\OrderInterface">
        <attribute code="custom_col1" type="string">
            <join reference_table="custom_table" join_on_field="entity_id" reference_field="order_id">
                <field column="custom_col1">custom_col1</field>
            </join>
        </attribute>
        <attribute code="custom_col2" type="string">
            <join reference_table="custom_table" join_on_field="entity_id" reference_field="order_id">
                <field column="custom_col2">custom_col1</field>
            </join>
        </attribute>
    </extension_attributes>
</config>

3. Apply fix for OrderRepository (see magento/magento2#8035: Join extension attributes are not added to Order results (REST api) #21797)
4. Check SQL query for loaded collection via (\Magento\Sales\Model\OrderRepository::getList)

public function getList(\Magento\Framework\Api\SearchCriteriaInterface $searchCriteria)
    {
        /** @var \Magento\Sales\Api\Data\OrderSearchResultInterface $searchResult */
        $searchResult = $this->searchResultFactory->create();
        /** @var \Magento\Framework\Api\ExtensionAttribute\JoinProcessor $extensionJoinProcessor */
        $extensionJoinProcessor = ObjectManager::getInstance()->get('\Magento\Framework\Api\ExtensionAttribute\JoinProcessor');
        $extensionJoinProcessor->process($searchResult);
        $this->collectionProcessor->process($searchCriteria, $searchResult);
        $searchResult->setSearchCriteria($searchCriteria);
        
        echo (string)$searchResult->getSelect();
        die;
        foreach ($searchResult->getItems() as $order) {
            $this->setShippingAssignments($order);
            $this->setOrderTaxDetails($order);
            $this->setPaymentAdditionalInfo($order);
        }
        return $searchResult;
    }

5. You will see two left joins of the same table with the same conditions instead of single one. This will cause performance degradation with every added extension attribute.

Proposed solution

Create table aliases base on table name to be joined and join conditions in
\Magento\Framework\Api\ExtensionAttribute\JoinProcessorHelper::getReferenceTableAlias

[m2-assistant]

Hi @swnsma. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento-engcom-team give me 2.3-develop instance - upcoming 2.3.x release

For more details, please, review the Magento Contributor Assistant documentation.

@swnsma do you confirm that you was able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• yes
• no

[swnsma]

Double checked:
In case of 1-1 relation, MySQL is optimized enough to handle multiple number of left joins without performance degradation.
Other cases is not relevant for extension attributes join directive.