1

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

Daniel Cruz
  • 1,437
  • 3
  • 5
  • 19
  • 1
    I've been trying to solve a very similar problem. From what I've learned so far we need to use TypeORM's Query Builder, the repository won't help us on this. My best hint so far involves creating an Insert with the query builder and then using SubQueries to try to retrieve the values I want to insert from other tables. I cant seem to make it work though – PedroDM Feb 25 '22 at 14:57

0 Answers0