$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 = "" )