How to insert without values

[lueenavarro]

Issue type:

[ x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x ] latest
[ ] @next
[ ] 0.x.x (or put your version here)

I need to generate the following query using typeorm:

INSERT INTO `invoice_id`(`invoiceId`)
WITH RECURSIVE cte AS
(
  SELECT 1 AS i
  UNION ALL
  SELECT i+1
  FROM cte
  WHERE i < 10
)
SELECT *
FROM cte

I understand that the WITH part is hard / impossible to do in typeorm, but at least I want to do the INSERT INTO `invoice_id`(`invoiceId`) the typeorm way.

The documentation only shows how to do raw queries with values, but I do not use the VALUES keyword here.

This is my initial code:

this.createQueryBuilder()
      .insert()
      .into(InvoiceId)
      .values({
        invoiceId: () => `
        WITH RECURSIVE cte AS
        (
           SELECT 1 AS i
           UNION ALL
           SELECT i+1
           FROM cte
           WHERE i < 10
        )
        SELECT *
        FROM cte`,
      });

which results to this:

INSERT INTO `invoice_id`(`invoiceId`) VALUES (
        WITH RECURSIVE cte AS
        (
           SELECT 1 AS i
           UNION ALL
           SELECT i+1
           FROM cte
           WHERE i < 10
        )
        SELECT *
        FROM cte)

I just want to remove the enclosing VALUES keyword, also I need to replace 1 and 10 with parameters.

[imnotjames]

This isn't possible with the query builder as it exists today. You're best off writing your own queries for something that complex.

[imnotjames]

For questions, please check out the community slack or check TypeORM's documentation page on other support avenues - cheers!