Insert using a select in typeorm

Question

I want to reproduce a query where the values are the result of a select in typeorm. The query i want to reproduce is the one i provide here, but i can't find anything in typeorm documentation. (Isnt important what the query does for the answer, i only need to know how to write that "SELECT" in typeorm)

http://typeorm.delightful.studio/classes/_query_builder_insertquerybuilder_.insertquerybuilder.html#values

INSERT INTO `furgpezzo`(`giacenza`, `giacenzaMin`, `pezzoBarcode`, `furgoneTarga`, `invStandardId`) 
    select '0', '5', '234234234234', f.`furgoneTarga`, '1'
    from `furgpezzo` f
    where f.`invStandardId` = '1'
    group by f.`furgoneTarga`

something like:

(Edit:)

return await this.dmDatabase.getRepository(FurgPezzo)
    .createQueryBuilder()
    .insert()
    .into(FurgPezzo)
    .values(   //here put my select   )

score 8 · Answer 1 · edited Oct 15 '22 at 03:34

8

This is the neatest solution I could come up with, hope it helps future explorers. The answer comments inspired me.

const [selectQuery, params] = this.entityManager
  .createQueryBuilder()
  .select("user.name")
  .from(User, "user")
  .where("user.id IN (:...ids)", {
    ids: [
      "87654321-1234-1234-1234-123456789abc",
      "a9876521-aabb-ccdd-ffaa-123abcdefccc",
    ],
  })
  .getQueryAndParameters();

await this.entityManager.query(
  `
INSERT INTO dummy("name")
${selectQuery}
`,
  params
);

edited Oct 15 '22 at 03:34

Dhia Djobbi

1,176
2
15
35

answered Mar 08 '21 at 11:32

Ertan Kara

308
3
12

This does not work because generated query does not preserve column order of your select statement. – Vicary Aug 23 '22 at 06:27

score -2 · Accepted Answer · answered Aug 06 '19 at 02:53

-2

Yes you can. From docs:

You can easily create subqueries. Subqueries are supported in FROM, WHERE and JOIN expressions.

Refer to the following posts for example: TypeORM subqueries, Typeorm subquery add select.

You can use subqueries:

return await this.dmDatabase.getRepository(InvStandard)
    .insert()
    .values(qb => {qb.select(FurgPezzo).where()})//here put my select 

// with subquery->

return await this.dmDatabase.getRepository(InvStandard)
    .insert()
    .values(qb => {qb.select(FurgPezzo).where(
            const subQuery = qb.subQuery()
                // your subquery builder
            return "your condition " + subQuery;)})

answered Aug 06 '19 at 02:53

Shinjo

677
6
22

I wrote a bad answer... I have to edit it. What i want was use that subquery on "values". i can't find a way – marco-zan reymerk Aug 06 '19 at 07:45
@MarcoZanonfurbino1 Not sure if it's possible to use on values using the current template. But possible alternative I can think of is using the raw query and run it; example: `await manager.query('INSERT statement... select statement...')` – Shinjo Aug 06 '19 at 08:10
I did so and the problem is solved for now. Thank you very much – marco-zan reymerk Aug 06 '19 at 13:59
4

This answer is out of context – Vincent Guyard Mar 19 '21 at 04:00
8

`.values()` doesn't even take queryBuilder as parameters. – Vicary Sep 15 '21 at 08:44
not sure why this is the accepted answer, it doesn't work as mentioned by @Vicary. Was this valid at the time? – Samathingamajig Jun 29 '22 at 16:55
@Shinjo please remove your answer, TypeORM doesn't allow subqueries for values. Just check the doc and test the code. – Robin Goupil Aug 11 '22 at 16:10

Insert using a select in typeorm

2 Answers2

Linked