javascript - Knex.js 更新语句从多个表返回数据

Question

如何使用 knex.js 执行更新语句，其中返回数据来自多个表。例如，我将如何使用 knex.js 编写此更新查询？

update notes 
set note = 'This is an updated1 note', timestamp = now() 
from(select
    id, 
    note, 
    user_id,
    customer_id
    from notes
    where id = '46' AND user_id = 7) a
    left join customers as b on a.customer_id = b.id
where notes.id = a.id
returning a.id, a.note, a.user_id, b.id;

我可以写它在只使用一张表时返回数据:

knex('notes')
    .returning([
      'notes.id',
      'notes.note',
      'notes.user_id AS userId',
      'notes.customer_id AS customerId',
      'notes.product_id AS productId',
      'notes.timestamp',
    ])
    .where('notes.id', noteId)
    .update({
        timestamp: new Date(),
        note: req.body.note,
    });

当我尝试介绍客户时，我的问题就开始了。

我也尝试过这个:

const [note] = await db.sequelize.knex.raw(
        'UPDATE notes '
        + 'SET note = ?, '
        + 'timestamp = NOW() '
        + 'FROM(SELECT id, '
        + 'note, '
        + 'user_id, '
        + 'customer_id '
        + 'FROM notes '
        + 'WHERE id = ? '
        + 'AND user_id = 7) AS a '
        + 'LEFT JOIN customers AS b ON a.customer_id = b.id '
        + 'WHERE notes.id = a.id '
        + 'RETURNING a.id, a.note, a.user_id, b.id', ['This is an updated1 note', 46]
      );

但它给了我TypeError:(中间值)不可迭代。

更新

我现在得到了这个:

let note = await db.sequelize.knex.raw(
        'UPDATE notes '
        + 'SET note = ?, '
        + 'timestamp = NOW() '
        + 'FROM(SELECT id, '
        + 'note, '
        + 'user_id, '
        + 'customer_id, '
        + 'product_id, '
        + 'timestamp '
        + 'FROM notes '
        + 'WHERE id = ? '
        + 'AND user_id = 7) AS a '
        + 'LEFT JOIN customers AS b ON a.customer_id = b.id '
        + 'LEFT JOIN products AS c ON a.product_id = c.id '
        + 'WHERE notes.id = a.id '
        + 'RETURNING a.id, a.note, a.user_id AS userId, a.customer_id AS customerId, a.product_id AS productId, a.timestamp, b.title AS customerName, b.code AS customerCode, c.title AS productName, c.code AS productCode', [req.body.note, noteId]
      );

      /*const [note] = await db.sequelize.knex('notes')
        .returning([
          'notes.id',
          'notes.note',
          'notes.user_id AS userId',
          'notes.customer_id AS customerId',
          'notes.product_id AS productId',
          'notes.timestamp',
          //'customers.title AS customerName',
          //'customers.code AS customerCode',
          //'products.title AS productName',
          //'products.code AS productCode',
        ])
        //.leftJoin('customers', 'notes.customer_id', 'customers.id')
        //.leftJoin('products', 'notes.product_id', 'products.id')
        .where('notes.id', noteId)
        .update({
          timestamp: new Date(),
          note: req.body.note,
        });*/

        console.log('PC NOTE', note.rows);
      [note] = note.rows;

      return res.json({ note });

它返回:

{
    "note": {
        "id": 46,
        "note": "This is an updated2 note",
        "userid": 7,
        "customerid": 111781,
        "productid": null,
        "timestamp": "2019-07-12T19:37:23.996Z",
        "customername": "PC CUSTOMER3",
        "customercode": "PC003",
        "productname": null,
        "productcode": null
    }
}

这几乎是我所需要的，除了返回的键都是小写的。如何获取 userid、customerid、productid、customername、customercode、productname 和 productcode 出现在驼峰式大小写中，就像我在 knex 语句的 RETURN 部分中所述？

Answer 1

Knex 对更新+连接没有任何特殊支持

https://github.com/tgriesser/knex/issues/2796

因此编写原始查询是您唯一的选择。