Sequelize create row with join in column-CodePudding

| userID (PK)  | user_role_ID(FK)    |
| -------------| --------------------|
| 77888        | 1                   |
| 65655        | 1                   |

Here is how my tables are built:

| role_ID(PK)    | role_name     |
| ---------------| --------------|
| 1              | Admin         |
| 2              | View          |

The tables are connected by user_role_id and role_id I need to create new records in Users Table, I have the user_role value.

Is there a way to shortcut the way to get the value of the user_role_id, or the only way to do it is a seperate query before creating new record in Users?

const newRole = "Admin";

Users.create({
        user_id:"34343",
        user_role_id: ??? (newRole ID)
      })

CodePudding user response：

You can do something like this: INSERT/SELECT using sequelize.query() to run a raw query and pass in the values you want as replacements.

const newId = "34343";
const newRole = "Admin";

await sequelize.query(
  'INSERT INTO Users (userID, user_role_ID) 
   SELECT ?, role_ID FROM Roles WHERE role_name = ?',
  {
    replacements: [newId, newRole]
  }
);

References here.