Vladimir Fishchenko
In the previous post, we’ve seen how we can join table using extension attribute.
It makes data always joined and available as an instance of your data interface within getExtensionAttributes method.
The advantage of the method explained in this post is that a table will be joined only when you need to filter or sort the getList data.
HOW TO ACCESS
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
To join your table, you need to implement Magento\Framework\Api\SearchCriteria\CollectionProcessor\JoinProcessor\CustomJoinInterface::apply(AbstractDb $collection).
Inside you can use $collection->join(…) method, but it this example I’m going to use $collection->joinExtensionAttribute(…) method to make level of code higher.
In the example, we have a custom order transaction table and we want to filter getList result to get only items with transactions.
Create OrderTransaction custom join
<?php
declare(strict_types=1);
namespace VF\JoinExample\Model\CollectionProcessor\JoinProcessor;
use Magento\Framework\Api\ExtensionAttribute\JoinProcessorInterface;
use Magento\Framework\Api\SearchCriteria\CollectionProcessor\JoinProcessor\CustomJoinInterface;
use Magento\Framework\Api\ExtensionAttribute\JoinDataInterfaceFactory;
use Magento\Framework\Data\Collection\AbstractDb;
use Magento\Sales\Api\Data\OrderInterface;
use VF\JoinExample\Api\Data\OrderTransactionInterface;
class OrderTransaction implements CustomJoinInterface
{
/**
* @var JoinDataInterfaceFactory
*/
private $joinDataFactory;
/**
* @var JoinProcessorInterface
*/
private $joinProcessor;
/**
* OrderTransaction constructor.
*
* @param JoinDataInterfaceFactory $joinDataFactory
* @param JoinProcessorInterface $joinProcessor
*/
public function __construct(
JoinDataInterfaceFactory $joinDataFactory,
JoinProcessorInterface $joinProcessor
) {
$this->joinDataFactory = $joinDataFactory;
$this->joinProcessor = $joinProcessor;
}
/**
* @inheritDoc
*/
public function apply(AbstractDb $collection)
{
$joinData = $this->joinDataFactory->create();
$joinData->setJoinField(OrderInterface::ENTITY_ID)
->setReferenceTable('vf_order_transaction')
->setReferenceField(OrderTransactionInterface::ORDER_ID)
->setReferenceTableAlias('transaction')
->setSelectFields([]);
$collection->joinExtensionAttribute($joinData, $this->joinProcessor);
}
}
Then in di.xml:
Create a virtual type of search criteria join processor and add here your custom join
<virtualType name="VF\JoinExample\Model\CollectionProcessor\JoinProcessor" type="Magento\Framework\Api\SearchCriteria\CollectionProcessor\JoinProcessor">
<arguments>
<argument name="customJoins" xsi:type="array">
<item name="loyalty_transaction.transaction_id" xsi:type="object">VF\JoinExample\Model\CollectionProcessor\JoinProcessor\OrderTransaction</item>
</argument>
<argument name="fieldMapping" xsi:type="array">
<item name="extension_attribute_loyalty_transaction.transaction_id" xsi:type="string">transaction.transaction_id</item>
</argument>
</arguments>
</virtualType>
Create a virtual type of search criteria collection processor and add virtual join processor
<virtualType name="VF\JoinExample\Model\CollectionProcessor" type="Magento\Framework\Api\SearchCriteria\CollectionProcessor">
<arguments>
<argument name="processors" xsi:type="array">
<item name="join" xsi:type="object">VF\JoinExample\Model\CollectionProcessor\JoinProcessor</item>
</argument>
</arguments>
</virtualType>
Add virtual collection processor to order repository
<type name="Magento\Sales\Model\OrderRepository">
<arguments>
<argument name="collectionProcessor" xsi:type="object">VF\JoinExample\Model\CollectionProcessor</argument>
</arguments>
</type>
Or use a virtual type of order repository if you need to use it only once.
Now, if you add a field as a filter (or sort field) it will be joined automatically to the collection.
In our example it could look like:
<?php
$searchCriteriaBuilder = $this->searchCriteriaBuilderFactory->create();
$searchCriteriaBuilder->addFilter('transaction.transaction_id', true, 'notnull');
$orderList = $this->orderRepository->getList($searchCriteriaBuilder->create());
SQL query will look like:
SELECT main_table.* FROM sales_order AS main_table
LEFT JOIN vf_order_transaction AS loyalty_transaction
ON main_table.entity_id = loyalty_transaction.order_id
WHERE ((loyalty_transaction.transaction_id IS NOT NULL))
Without a filter is still be the same as in the start of the post.
As you see, there is no joined fields selected and they are used only for filtering or sorting.
Since there are no way to say which fields you want to select in a repository getList method and table is not joined if you don’t filter or sort, I don’t think it’s a good idea to select any joined fields using CustomJoinInterface.