Knex.js从选择中插入

Question

我正在尝试通过 Knex.js 生成如下所示的查询：

    INSERT INTO table ("column1", "column2")
    SELECT "someVal", 12345
    WHERE NOT EXISTS (
        SELECT 1
        FROM table
        WHERE "column2" = 12345
    )

基本上，我只想仅在特定值尚不存在时才插入值。但 Knex.js 似乎不知道该怎么做;如果我调用knex.insert()（没有值），它会生成一个"插入默认值"查询。

我尝试了以下方法：

    pg.insert()
        .into(tableName)
        .select(_.values(data))
        .whereNotExists(
            pg.select(1)
                .from(tableName)
                .where(blah)
        );

但这仍然只是给了我默认值的东西。我试着添加一个.columns(Object.keys(data))，希望insert()会尊重这一点，但没有运气。

是否可以使用 knex 生成我想要的查询，或者我只需要在没有 Knex.js 方法的情况下构建一个原始查询？

Answer 1

我相信选择需要传递到插入中：

pg.insert(knex.select().from("tableNameToSelectDataFrom")).into("tableToInsertInto");

此外，为了选择常量值或表达式，您需要在选择中使用 knex.raw 表达式：

knex.select(knex.raw("'someVal',12345")).from("tableName")

这是我的第一篇文章，我没有测试您的具体示例，但是我已经做了类似的事情，例如您使用上述技术提出的问题。

Answer 2

我找到的最全面的答案（INSERT的显式列名和 SELECT 语句中的自定义值）在这里：

https://github.com/knex/knex/issues/1056#issuecomment-156535234

^{作者：克里斯·布鲁姆}

下面是该解决方案的副本：

const query = knex
  // this part generates this part of the
  // query: INSERT "tablename" ("field1", "field2" ..)
  .into(knex.raw('?? (??, ??)', ['tableOrders', 'field_user_id', 'email_field']))
  // and here is the second part of the SQL with "SELECT"
  .insert(function() {
    this
      .select(
        'user_id',  // select from column without alias
        knex.raw('? AS ??', ['[e-mail]', 'email']),  // select static value with alias
      )
      .from('users AS u')
      .where('u.username', 'jdoe')
   });
console.log(query.toString());

和 SQL 输出：

insert into "orders" ("user_id", "email")
  select "user_id", '[e-mail]' AS "email"
  from "users" as "u"
  where "u"."username" = 'jdoe'

<小时 />

另一种方法（由 Knex 开发人员提供）：https://github.com/knex/knex/commit/e74f43cfe57ab27b02250948f8706d16c5d821b8#diff-cb48f4af7c014ca6a7a2008c9d280573R608 - 也使用 knex.raw

Answer 3

我已经设法让它在我的项目中工作，而且看起来并不那么糟糕！

        .knex
            .into(knex.raw('USER_ROLES (ORG_ID, USER_ID, ROLE_ID, ROLE_SOURCE, ROLE_COMPETENCE_ID)'))
            .insert(builder => {
                builder
                    .select([
                        1,
                        'CU.USER_ID',
                        'CR.ROLE_ID',
                        knex.raw(`'${ROLES.SOURCE_COMPETENCE}'`),
                        competenceId,
                    ])
                    .from('COMPETENCE_USERS as CU')
                    .innerJoin('COMPETENCE_ROLES as CR', 'CU.COMPETENCE_ID', 'CR.COMPETENCE_ID')
                    .where('CU.COMPETENCE_ID', competenceId)
                    .where('CR.COMPETENCE_ID', competenceId);

请注意，目前这似乎不能与 MSSQL 上的返回子句正常工作（它只是被 Knex 忽略了）。