Lets say I have main table Users and a reference table UserRoles such that each User has one Role saved in the column roleId, and every role has an id and a description.
If I want to insert a new User with the role Administrator, the following SQL query would work:
INSERT INTO `users` (`name`, `roleId`)
VALUES ('John Doe', (SELECT `id` FROM `roles` WHERE `roles`.`description` = 'admin'));
How would I replicate the same in TypeORM having two entities User and Role?
I know I can do the following:
const adminRole = await Role.findOne({description: 'admin'});
const newUser = User.create({name: 'John Doe'});
newUser.role = adminRole;
await newUser.save();
But this would rather be equivalent to SELECT the role into a variable and then using that variable during the INSERT. is there a way to condense this into one query so that the database is hit only once?
I know that I can create an user with the relation like this
User.create({name: 'John Doe', role: {id: 1}});
But I need to know the id for this. If I put the name like the following
User.create({name: 'John Doe', role: {name: 'admin'}});
I get the following error Error: Cannot find alias for relation at type
because I have not loaded the relation.
I've found information on how to make an INSERT INTO SELECT statement here, but this is where the whole insert comes from a select, not just a particular column.
Is there a way to emulate this query for insertions? or I'm forced to either do it in two steps (or as many as reference columns as I have) or use the query builder?
Thanks you in advance