Emulating row_number function.

[clodoaldofavaro]

Hello there, how is it going?

I've run into a interesting puzzle trying to emulate the row_number function.

With raw SQL, I found how to do it in two ways:

select row_number as seqItem

from 

	(select
		`i`.`id`,
		@curRow := @curRow + 1 AS row_number
	from
		`orderitem` as `i`,
		(select @curRow := 0) as `r`
	where
		`i`.`idCompany` = '123'
		and `i`.`idOrder` = '456'
	) sub	

where sub.id = '789'; 

or

select row_number as seqItem

from 

	(select
		`i`.`id`,
		@curRow := @curRow + 1 AS row_number
	from
		`orderitem` as `i`
	inner join
		(select @curRow := 0) as `r`
	where
		`i`.`idCompany` = '123'
		and `i`.`idOrder` = '456'
	) sub	

where sub.id = '789'; 

I've tried doing the following

$subSelect = $qb->table(['orderitem' => 'i', '(select @curRow := 0)' => 'r'])->select('i.id', $qb->raw('@curRow := @curRow + 1 AS row_number'))
		  ->where('i.idCompany', $idCompany)
		  ->where('i.idOrder', $idOrder);

$qb = $qb->table(['sub' => $subSelect])->select(['row_number' => 'seqItem'])->where('sub.id', $idOrderItem);

which resulted in the query string

select
	`row_number` as `seqItem`
from
	(
	select
		`i`.`id`,
		@curRow := @curRow + 1 AS row_number
	from
		`orderitem` as `i`,
		`(select @curRow := 0)` as `r`
	where
		`i`.`idCompany` = '123'
		and `i`.`idOrder` = 456) as `sub`
where
	`sub`.`id` = 789;

and it resulted in a error because of the backticks in

`(select @curRow := 0)`

Is this not supported, or I'm just missing something?

Best regards!