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.
