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

|improve this answer|||||
  • Thank you. This solution works for me. :) – keshab karmakar Nov 22 '19 at 10:22
1

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.

|improve this answer|||||
  • Views are great in principal however at the moment cannot have an index in the same way a standard table can and therfore by design are inefficient and slow – Dominic Xigen Nov 6 '19 at 21:29

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.