Vladimir Fishchenko

Magento Developer since 2010, Magento Maintainer
Dublin, Ireland

github linkedin email
[Magento 2] Join table in OrderRepository::getList - extension attributes
Jul 4, 2019
3 minutes read

When you use OrderRepositoryInterface::getList method without any filters and orders,
SQL query will look like SELECT main_table.* FROM sales_order AS main_table

One of the ways to join data to this table is the usage of extension attributes. Link to official devdocs.
It also provides a nice interface accessing data using getExtensionAttributes.

The usual process of adding extension attributes includes only adding configuration to extension_attributes.xml.
Example:

<?xml version="1.0"?>
<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 type="VF\JoinExample\Api\Data\OrderTransactionInterface" code="order_transaction">
            <join reference_table="vf_order_transaction" join_on_field="entity_id" reference_field="order_id">
                <field>order_id</field>
                <field>transaction_id</field>
            </join>
        </attribute>
    </extension_attributes>
</config>

where VF\JoinExample\Api\Data\OrderTransactionInterface is a data interface for your table entity.

This would be enough for product or customer repository, but the order repository doesn’t include extension attribute join processor, which is required to make the magic work.

Let’s take a look into getList method (Magento 2.3.1) to find where we can add it.

<?php
    /**
     * Find entities by criteria
     *
     * @param \Magento\Framework\Api\SearchCriteriaInterface $searchCriteria
     * @return \Magento\Sales\Api\Data\OrderSearchResultInterface
     */
    public function getList(\Magento\Framework\Api\SearchCriteriaInterface $searchCriteria)
    {
        /** @var \Magento\Sales\Api\Data\OrderSearchResultInterface $searchResult */
        $searchResult = $this->searchResultFactory->create();
        $this->collectionProcessor->process($searchCriteria, $searchResult);
        $searchResult->setSearchCriteria($searchCriteria);
        foreach ($searchResult->getItems() as $order) {
            $this->setShippingAssignments($order);
            $this->setOrderTaxDetails($order);
            $this->setPaymentAdditionalInfo($order);
        }
        return $searchResult;
    }

The result is iterated inside the method which means that data is loaded, so we cannot add extension join processor after method call / in after plugin.

We also cannot add it before method call because the search result is initiated inside the method.

Effectively, we should add extension join processor somewhere between $this->searchResultFactory->create() and $searchResult->getItems().

The easiest way to add join processor could be around plugin or class/method rewrite, but methods used inside foreach are private and it is too complicated to copy them to your rewrite/plugin class.

If you will dig deeper you’ll find collectionProcessor iterates through implementations of CollectionProcessorInterface whose can be provided in the constructor.

We are going to create our own implementation of CollectionProcessorInterface and add it to the collection processor, so it will be executed with every getList run.

ExtensionAttributeJoinProcessor:

<?php

declare(strict_types=1);

namespace VF\JoinExample\Model\OrderRepository\CollectionProcessor;

use Magento\Framework\Api\ExtensionAttribute\JoinProcessorInterface;
use Magento\Framework\Api\SearchCriteria\CollectionProcessorInterface;
use Magento\Framework\Api\SearchCriteriaInterface;
use Magento\Framework\Data\Collection\AbstractDb;

class ExtensionAttributeJoinProcessor implements CollectionProcessorInterface
{
    /**
     * @var JoinProcessorInterface
     */
    private $joinProcessor;

    /**
     * ExtensionAttributeJoinProcessor constructor.
     * @param JoinProcessorInterface $joinProcessor
     */
    public function __construct(
        JoinProcessorInterface $joinProcessor
    ) {
        $this->joinProcessor = $joinProcessor;
    }

    /**
     * @inheritDoc
     */
    public function process(SearchCriteriaInterface $searchCriteria, AbstractDb $collection)
    {
        $this->joinProcessor->process($collection);
    }
}

Lines in di.xml:

    <type name="Magento\Sales\Model\OrderRepository">
        <arguments>
            <argument name="collectionProcessor" xsi:type="object">VF\JoinExample\Model\OrderRepository\CollectionProcessor</argument>
        </arguments>
    </type>
    <virtualType name="VF\JoinExample\Model\OrderRepository\CollectionProcessor" type="Magento\Framework\Api\SearchCriteria\CollectionProcessor">
        <arguments>
            <argument name="processors" xsi:type="array">
                <item name="extensionAttributesJoin" xsi:type="object">VF\JoinExample\Model\OrderRepository\CollectionProcessor\ExtensionAttributeJoinProcessor</item>
            </argument>
        </arguments>
    </virtualType>

After that, your table will be always joined to order table when use getList method.
SQL query without filters and sort order will look like:

SELECT 
  main_table.*, 
  extension_attribute_order_transaction.order_id AS extension_attribute_order_transaction_order_id, 
  extension_attribute_order_transaction.transaction_id AS extension_attribute_order_transaction_transaction_id 
  FROM sales_order AS main_table
LEFT JOIN vf_order_transaction AS extension_attribute_order_transaction 
  ON main_table.entity_id = extension_attribute_order_transaction.order_id

and your data model can be accessed as:

<?php 
$orderList = $this->orderRepository->getList($searchCriteria);
foreach ($orderList->getItems() as $order) {
    /** @var ?\VF\JoinExample\Api\Data\OrderTransactionInterface $orderTransaction */
    $orderTransaction = $order->getExtensionAttributes()->getOrderTransaction();
}

Back to posts


comments powered by Disqus