Using @variables MySQL in Sequelize

Question

I'm trying to get a iterator from MySQL (Sequelize Raw Query), using SET variables, as follows:

SET @Variable:=0; SELECT @Variable:=@Variable+1 AS AddNewColumn

My specific code is:

SET @i = 0; SELECT @i:=@i+1 AS iterator, COUNT(ci.id) totalCases, ...

But throws the following error:

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'SELECT\n @i:=@i+1 AS iterator,\n COUNT(ci.id) totalCases,\n SUM(b_p.amount\' at line 2',

Any idea?

By the way, I'm using MySQL 5.7:

mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper

Answer 1

SOLVED 05/17/2019 14:12

In my raw query I'm assign @rownum:=0 in FROM, targeting the table before declarating, as follow:

FROM (SELECT @rownum:=0) case_imports, case_imports ci

And at beginning:

SELECT (@rownum:=@rownum+1) AS id, COUNT(ci.id) totalCases,

Finally, ordering by id:

ORDER BY id

Answer 2

I found you can also use variables in Sequelize by breaking each SQL statement, including the variable declaration, into a separate query:

await sequelize.query('SET @i = 0;');
const rows = await sequelize.query(
    'SELECT @i := @i + 1 AS iterator FROM someTable;',
    { type: sequelize.QueryTypes.SELECT },
);

Or if you'd like to use a transaction:

await sequelize.transaction(async (transaction) => {
    await sequelize.query('SET @rank = -1;', { transaction });
    const querySql = `
        UPDATE someTable
            SET rank = (@rank := @rank + 1)
            WHERE groupId = :groupId
            ORDER BY totalPoints DESC, age ASC;`;
    await sequelize.query(querySql, {
        transaction,
        replacements: { groupId },
    });
});

This was tested in [email protected], and I'm unsure if it changes in later versions.