Magento 2 : How to add sub query in custom collection

Question

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.

Answer 1

    $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.