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();
}