title: How to INSERT with SELECT values in TypeORM
body: <p>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.</p>
  <p>If I want to insert a new User with the role Administrator, the following SQL query would work:</p>
  <pre><code>INSERT INTO `users` (`name`, `roleId`) 
  VALUES ('John Doe', (SELECT `id` FROM `roles` WHERE `roles`.`description` = 'admin'));
  </code></pre>
  <p>How would I replicate the same in TypeORM having two entities User and Role?</p>
  <p>I know I can do the following:</p>
  <pre><code>const adminRole = await Role.findOne({description: 'admin'});
  const newUser = User.create({name: 'John Doe'});
  newUser.role = adminRole;
  await newUser.save();
  </code></pre>
  <p>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?</p>
  <p>I know that I can create an user with the relation like this</p>
  <pre><code>User.create({name: 'John Doe', role: {id: 1}});
  </code></pre>
  <p>But I need to know the id for this. If I put the name like the following</p>
  <pre><code>User.create({name: 'John Doe', role: {name: 'admin'}});
  </code></pre>
  <p>I get the following error <code>Error: Cannot find alias for relation at type</code> because I have not loaded the relation.</p>
  <p>I've found information on how to make an INSERT INTO SELECT statement <a href="https://stackoverflow.com/q/57363201/17537072">here</a>, but this is where the whole insert comes from a select, not just a particular column.</p>
  <p>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?</p>
  <p>Thanks you in advance</p>