cancel
Showing results for 
Search instead for 
Did you mean: 

Custom Joins with Join Processor

Custom Joins with Join Processor

Hey!

I'm using Magento ver. 2.4.2 and try join two tables using Custom Joins

 

According this example

I use it this way di.xml:

 

    <virtualType name="Vendor\Module\Model\CollectionProcessor\JoinProcessor" type="Magento\Framework\Api\SearchCriteria\CollectionProcessor\JoinProcessor">
        <arguments>
            <argument name="customJoins" xsi:type="array">
                <item name="rule_id" xsi:type="object">Vendor\Module\Model\CollectionProcessor\JoinProcessor\Product</item>
            </argument>
        </arguments>
    </virtualType>
    
    <virtualType name="Vendor\Module\Model\CollectionProcessor" type="Magento\Framework\Api\SearchCriteria\CollectionProcessor">
        <arguments>
            <argument name="processors" xsi:type="array">
                <item name="join" xsi:type="object">Vendor\Module\Model\CollectionProcessor\JoinProcessor</item>
            </argument>
        </arguments>
    </virtualType>
    
    <type name="Vendor\Module\Model\ProductAttributesRepository">
        <arguments>
            <argument name="collectionProcessor" xsi:type="object">Vendor\Module\Model\CollectionProcessor</argument>
        </arguments>
    </type>

 

My Custom Join:

 

namespace Vendor\Module\Model\CollectionProcessor\JoinProcessor;

use ...

class Product implements \Magento\Framework\Api\SearchCriteria\CollectionProcessor\JoinProcessor\CustomJoinInterface;
{
    public function apply(\Magento\Framework\Data\Collection\AbstractDb $collection)
    {
        $collection->getSelect()->joinLeft(
            ['product_action' => 'action_product'],
            "main_table.rule_id = product_action.entity_id",
            []
        );

        return true;
    }
}

 

Using in Collection Processor in Repository:

 

    public function __construct(
        \Magento\Framework\Api\SearchCriteriaBuilder\SearchCriteriaBuilder $searchCriteriaBuilder,
        \Magento\Framework\Api\SearchCriteria\CollectionProcessorInterface $collectionProcessor,
        \Magento\Framework\Api\ExtensionAttribute\JoinProcessorInterface $extensionAttributesJoinProcessor
    ) {
        $this->searchCriteriaBuilder = $searchCriteriaBuilder;
        $this->collectionProcessor = $collectionProcessor;
        $this->extensionAttributesJoinProcessor = $extensionAttributesJoinProcessor;
    }

    public function getByRuleId(int $ruleId): array
    {
        $searchCriteria = $this->searchCriteriaBuilder->addFilter(
            'rule_id',
            $ruleId
        )->create();
        return $this->getList($searchCriteria);
    }

    public function getList(\Magento\Framework\Api\SearchCriteriaInterface $searchCriteria)
    {
        $collection = $this->collectionFactory->create();
        $this->extensionAttributesJoinProcessor->process(
            $collection,
            \Vendor\Module\Api\Data\ProductAttributesInterface::class
        );
        $this->collectionProcessor->process($searchCriteria, $collection);

        // <-- Logger Place -->
        $writer = new \Zend\Log\Writer\Stream(BP . '/var/log/selector.log');
        $logger = new \Zend\Log\Logger();
        $logger->addWriter($writer);
        $logger->info('SQL: ' . $collection->getSelect()->__toString());
        
        return $collection->getItems();
    }

 

Test file:

 

require __DIR__ . '/../app/bootstrap.php';
$params = $_SERVER;
$params[\Magento\Store\Model\StoreManager::PARAM_RUN_CODE] = 'base';
$params[\Magento\Store\Model\StoreManager::PARAM_RUN_TYPE] = 'website';
$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $params);
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

$obj = $objectManager->get('\Vendor\Module\Api\ProductAttributesRepositoryInterface');
$obj->getByRuleId(1);
$obj->getByRuleId(2);

 

Result:

 

	Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rule_id' in 'where clause' in .../vendor/magento/framework/DB/Statement/Pdo/Mysql.php on line 126

	( ! ) Zend_Db_Statement_Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rule_id' in 'where clause', query was: SELECT `main_table`.* FROM `action_product_attributes` AS `main_table` WHERE ((`rule_id` = 2)) in ../vendor/magento/framework/DB/Statement/Pdo/Mysql.php on line 126

 

Result in Log file:

 

2021-07-01T14:56:57+00:00 INFO (6): SQL: SELECT `main_table`.* FROM `action_product_attributes` AS `main_table`
 LEFT JOIN `action_product` AS `product_action` ON main_table.rule_product_id = product_action.entity_id WHERE ((`rule_id` = 1))

2021-07-01T14:56:57+00:00 INFO (6): SQL: SELECT `main_table`.* FROM `action_product_attributes` AS `main_table` WHERE ((`rule_id` = 2))

 

 

 

Part from core what joining tables:

file: \Magento\Framework\Api\SearchCriteria\CollectionProcessor\JoinProcessor :: process

    public function process(SearchCriteriaInterface $searchCriteria, AbstractDb $collection)
    {
        if ($searchCriteria->getFilterGroups()) {
            //Process filters
            foreach ($searchCriteria->getFilterGroups() as $group) {
                foreach ($group->getFilters() as $filter) {
                    if (!isset($this->appliedFields[$filter->getField()])) {
                        $this->applyCustomJoin($filter->getField(), $collection);
                        $this->appliedFields[$filter->getField()] = true;
                    }
                }
            }
        }

        if ($searchCriteria->getSortOrders()) {
            // Process Sortings
            foreach ($searchCriteria->getSortOrders() as $order) {
                if (!isset($this->appliedFields[$order->getField()])) {
                    $this->applyCustomJoin($order->getField(), $collection);
                    $this->appliedFields[$order->getField()] = true;
                }
            }
        }
    }

So on first request `$this->appliedFields` not have my record about rule_id field, and then everything works. But in second request, core do not call a applyCustomJoin function because in `$this->appliedFields` already have this record, but collection is not have joined table yet...

 

Question:

How resolve the issue, when processing with second call of collection, not add Join part of SQL Query?

 

P.s.: To resolve that issue, i try add ' shared="false" ' on each DI element, but still not working...

 

BTW: I know how to join table in another way ...->getSelect()->join(array(... but I wonder what I’m doing wrong using this example.

 

Thanks.