Doctrine oridinal number with mysql (Error: Expected Literal, got '@')

Question

I need to have an oridinal number from mysql database. I found how emulate of row_num in mysql like this:

    SET @row=0;
    SELECT * FROM (
     SELECT (@row:=@row+1) AS no, id,  name FROM `attribute` ORDER BY id
     ) t WHERE name LIKE "%Jo%"

I begin code with:

$this->getEntityManager()->getConnection()->exec("SET @counter = 0");

and I tried:

        $this->result = $this->createQueryBuilder('a')
            ->select('a')
            ->where($expr->in('att.ordinal_number', $this->createQueryBuilder('att')->
                    select('(@counter:=@counter+1) AS ordinal_number')->
                    from(\App\Entity\Attribute::class, 'att')->
                    orderBy('att.id')->getDQL()))

and I tried:

        $this->result = $this->createQueryBuilder('a')
            ->select('a')
            ->addSelect('(SELECT (@counter:=@counter+1) AS oridinal_number, id, '
                    .' name FROM App:Entity:Atrribute ORDER BY id)')

Above give me:

Error: Expected Literal, got '@'

Anybody know how to emulate row_number in doctrine with mysql? Thanks in advance.

Answer 1

AFAIK there is no direct way to incorporate these DB variables in DQL or query builder, you will need to execute Native SQL and then use ResultSetMapping class to map the result of query to your entity

SQL

SELECT * 
FROM (
    SELECT (@row:=@row+1) AS no, 
            id,
            name
    FROM `attribute` ,(SELECT @row:=0) t
    ORDER BY id
) t WHERE name LIKE "%Jo%"

Resultset Mapping

$rsm = new ResultSetMapping;
$rsm->addEntityResult('Attribute', 'a');
$rsm->addFieldResult('a', 'id', 'id');
$rsm->addFieldResult('a', 'name', 'name');
$rsm->addScalarResult('no', 'no');

$query = $this->_em->createNativeQuery('SELECT * 
                                        FROM (
                                            SELECT (@row:=@row+1) AS no, 
                                                    id,
                                                    name
                                            FROM attribute ,(SELECT @row:=0) t
                                            ORDER BY id
                                        ) t WHERE name LIKE ?',$rsm);
$query->setParameter(1, '%Jo%');
$users = $query->getResult();