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!