0

Adding Join:

$subquery= new \Zend_Db_Expr('(SELECT @row_number := 0)');
$resourceCollection->join(array('payment'=>'sales_order_payment'),'main_table.entity_id=parent_id',['main_table.entity_id','(@row_number := @row_number +1) as num']);
$resourceCollection->join(['dummy'=>"$subquery"],array());

returns no record Sql Query :

SELECT `main_table`.*, `main_table`.`entity_id`, (@row_number := @row_number +1) AS `num`, `dummy`.* FROM `sales_order` AS `main_table` INNER JOIN `sales_order_payment` AS `payment` ON main_table.entity_id=parent_id CROSS JOIN `(SELECT @row_number := 0)` AS `dummy` WHERE (`status` = 'complete') AND (`main_table`.`created_at` >= '2022-03-01 00:00:00' AND `main_table`.`created_at` <= '2022-03-07 23:59:59') AND (main_table.store_id IN(1)) ORDER BY entity_id ASC

If I remove " ` " from sub query

CROSS JOIN (SELECT @row_number := 0) AS dummy

Then query works fine, please tell me how to remove ` from sub query or any other way to do.

1 Answer 1

0
    $select ="SELECT @row_number := 0";
    $resourceCollection->join(array('payment'=>'sales_order_payment'),'main_table.entity_id=parent_id',['main_table.entity_id','(@row_number := @row_number +1) as num']);
    $resourceCollection->getSelect()->join(['dummy'=>new \Zend_Db_Expr(" ( $select ) ")],array());

Tried this way and it worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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