Magento 2: How to add sub Query on left join

Question

$collection = $this->rmaLogsFactory->create();
$collection->addFieldToSelect('increment_id');
$collection->getSelect()->joinLeft('rma_log AS arl','main_table.increment_id = arl.order_id AND arl.order_id != "0" AND arl.message_type = "error"',['MAX(arl.created_at)','arl.description']);
$collection->getSelect()->joinLeft('rma_log_details','arl.id = rma_log_details.sync_record_id',['rma_log_details.long_message']);
$collection->getSelect()->where('main_table.status="authorized" AND main_table.failed_retry_count > 0 AND main_table.acumatica_order_id = "" ')->group('arl.order_id');
$this->setCollection($collection);

Above collection return below Query :

SELECT `main_table`.`increment_id`, MAX(arl.created_at), `arl`.`description`, `rma_log_details`.`long_message` FROM `magento_rma` AS `main_table` LEFT JOIN `rma_log` AS `arl` ON main_table.increment_id = arl.order_id AND arl.order_id != "0" AND arl.message_type = "error" LEFT JOIN `rma_log_details` ON arl.id = rma_log_details.sync_record_id WHERE (main_table.status="authorized" AND main_table.failed_retry_count > 0 AND main_table.acumatica_order_id = "" ) GROUP BY `arl`.`order_id`

But I need below query:

SELECT `main_table`.`increment_id`, `rma_log`.`description`
FROM `magento_rma` AS `main_table` 
LEFT JOIN `rma_log` ON main_table.increment_id = rma_log.order_id AND rma_log.order_id != "0" AND rma_log.message_type = "error" 
LEFT JOIN (SELECT max(created_at),order_id FROM rma_log GROUP BY order_id ) arl ON  rma_log.order_id = arl.order_id
WHERE (main_table.status="authorized" AND main_table.failed_retry_count > 0 AND main_table.acumatica_order_id = "" ) 

Answer 1

You will need to write something like this

$collection->getSelect()
    ->joinLeft(
        array('arl' => new Zend_Db_Expr(" ( $select ) ")),
        'rma_log.order_id = arl.order_id',
        array()
    );

Where $select is your inner query

Answer 2

It seems that you want to join to a table that is retrieving data from another table in your join.

In this case, i would suggest to use mysql view concept.

So login to your phpmyadmin and create a view using following syntax.

CREATE VIEW view_rma_log AS SELECT max(created_at) AS created at, order_id FROM rma_log GROUP BY order_id;

This view will be served as table but with dynamic records that you want. So now in your magento, you can left join with view_rma_log view/table and can get your desired result.

Hope it will help you.