Problem with raq SQL query in user-permissions register function

System Information
  • Strapi Version: 3.5.4
  • Operating System: localhost (Mac OSX 11.2.2 (20D80))
  • Database: 10.5.6-MariaDB-1:10.5.6
  • Node Version: 14.6.1
  • NPM Version: 6.14.12
  • Yarn Version: 1.22.10

Good morning,

in plugin user-permissions I want to execute a raw query for editing one many-to-many-table to insert a token. I have the following code:

[...]
const sanitizedUser = sanitizeEntity(user, {
    model: strapi.query('user', 'users-permissions').model,
});

user.companies.forEach(company => {
    const token = generator.generate({
        length: 64,
        numbers: true
    });

    let sql = `UPDATE companies_users__users_companies SET token = "${token}" WHERE user_id = ${user.id} AND company_id = ${company.id}`
    let res = strapi.connections.default.raw(sql);
})
[...]

The query is e.g.:
UPDATE companies_users__users_companies SET token = "2TjpKPAScGb7vDcjLukB6zbFqfHZsgW2pvZAg2apdiCx3ppt4IWVxm2oLa8JVmyx" WHERE user_id = 119 AND company_id = 3

But nothing happens. The field token is still “NULL”. If I execute the query directly in PhpMyAdmin, it works. I don’t see the problem. Does anybody have a hint for me?

Thank you!

Best,
Thorsten

Another idea was:

for await (const companies of user.companies.map(company =>  {
    const token = generator.generate({
        length: 64,
        numbers: true
    });

    let sql = `UPDATE companies_users__users_companies SET token = "${token}" WHERE user_id = ${user.id} AND company_id = ${company.id}`
    const res = strapi.connections.default.raw(sql);
}))

But this also has no effect.

Did I understand it right, you created manually a token Field inside the Relations table which contains only the ids of the relationship between companies and users? And you want to keep the token inside it? Not inside the companies table, and not inside the user’s table?

As a side note, I’m not sure how do you generate tokens, but maybe it requires to be promised? Can you try to replace your token function with a plain string?

const token = 'testing if token works';
let sql = `UPDATE companies_users__users_companies SET token = "${token}" WHERE user_id = ${user.id} AND company_id = ${company.id}`

Hi,

thanks for your reply. Yes, that’s right. There’s a many-to-many relation between companies and users. And every company has to confirm a user. That’s the short version.

I found the solution. I don’t know why, but I have to use .then():

let res = strapi.connections.default.raw(sql).then(
r => {},
error => {
});

With this code the SQL query is executed fine. :man_facepalming:

Because it actually returns a knex client which should be used with async/await or with .then, otherwise, it is not executed.

So just add async and await and it will work:

for await (const companies of user.companies.map(async (company) =>  { //added async here
    const token = generator.generate({
        length: 64,
        numbers: true
    });

    let sql = `UPDATE companies_users__users_companies SET token = "${token}" WHERE user_id = ${user.id} AND company_id = ${company.id}`
    const res = await strapi.connections.default.raw(sql); //added await here
}))

Thank you very much. It works like a charm. :grinning: