How can i execute something like full join in magento 2 then set query data in my collection. Below i try to do somethig.

$joinLeftCustomerSql = $connection
        ->from("$webSubscriberTable AS web_subscriber", ['token'])
        ->joinLeft(['customer' => $customerTable], 'web_subscriber.customer_id=customer.entity_id', 'email');

    $joinRightCustomerSql = $connection
        ->from("$webSubscriberTable AS web_subscriber", ['token'])
        ->joinRight(['customer' => $customerTable], 'web_subscriber.customer_id=customer.entity_id', 'email');

    $collection->getSelect()->reset()->union([$joinLeftCustomerSql, $joinRightCustomerSql]);

It is proper query but latter i have next problem No table has been specified for the FROM clause. After research i found out that magento can't do query COUNT(*) with my query and i need to build something like this:

SELECT * FROM (myquery) AS test

myquery - $collection->getSelect()->reset()->union([$joinLeftCustomerSql, $joinRightCustomerSql]);

and i try execute this query

$fullSqlToUnion = $connection->select()->union([$joinLeftCustomerSql, $joinRightCustomerSql]);


But it don't work because magento make quote around my query in FROM.


$collection->getSelect()->reset()->from($fullSqlToUnion, '*');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.