Vladimir Fishchenko

Magento Developer since 2010, Magento Maintainer
Dublin, Ireland

github linkedin email
[Magento 2] Join table in OrderRepository::getList - custom field
Jul 14, 2019
3 minutes read

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.


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



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();
        $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">
            <argument name="customJoins" xsi:type="array">
                <item name="loyalty_transaction.transaction_id" xsi:type="object">VF\JoinExample\Model\CollectionProcessor\JoinProcessor\OrderTransaction</item>
            <argument name="fieldMapping" xsi:type="array">
                <item name="extension_attribute_loyalty_transaction.transaction_id" xsi:type="string">transaction.transaction_id</item>

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">
            <argument name="processors" xsi:type="array">
                <item name="join" xsi:type="object">VF\JoinExample\Model\CollectionProcessor\JoinProcessor</item>

Add virtual collection processor to order repository

    <type name="Magento\Sales\Model\OrderRepository">
            <argument name="collectionProcessor" xsi:type="object">VF\JoinExample\Model\CollectionProcessor</argument>

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:

$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.

Back to posts

comments powered by Disqus