从sequelize raw查询返回更新的值

Question

我有一个使用原始查询来更新数据库的操作，我希望获得受查询影响的行作为输出。这个是可能的吗？

const op = (replacements, pgdb) { 
  const query = q(replacements);
  const args = [query, { replacements, type: Sequelize.QueryTypes.UPDATE, raw: true }];
  const results = await pgdb.query(...args);
  console.log(results);
  return results;
};

Answer 1

Postgresql可以通过RETURNING返回记录。

UPDATE T
SET ...
WHERE ...
RETURNING *

Answer 2

是的你可以。我正在对PostgreSQL数据库使用Sequelize。我的规格如下：

"pg"："^7.12.1“

"sequelize"："^5.21.2“

"sequelize-cli"："^5.5.1“

我在以下代码块中运行了一个更新查询：

const query = `UPDATE bookings b
    SET "isPaid" = :isPaid,
        "paymentType" = :paymentType
    FROM trips t,
         requests r
    WHERE "b"."tripId" = t.id
      AND r.id = "t"."requestId"
      AND r.id = :requestId
      AND "r"."userId" = :userId
    RETURNING b.id, "isPaid", "paymentType"`;

    return db.sequelize.query(
      query, {
        replacements: {
          isPaid, paymentType, requestId, userId
        }
      }
    );

我记录了结果：

console.log('resultFromQuery', resultFromQuery); 

并得到了以下结果：

resultFromQuery [
  [
    { id: 7, isPaid: true, paymentType: 'paypal' },
    { id: 8, isPaid: true, paymentType: 'paypal' }
  ],
  Result {
    command: 'UPDATE',
    rowCount: 2,
    oid: null,
    rows: [ [Object], [Object] ],
    fields: [ [Field], [Field], [Field] ],
    _parsers: [ [Function: parser], [Function: parser], [Function: parser] ],
    _types: TypeOverrides { _types: [Object], text: {}, binary: {} },
    RowCtor: null,
    rowAsArray: false
  }
]

然后，我能够获得rowCount，如下所示：

console.log('resultFromQuery', resultFromQuery[1].rowCount);

我还能够获得更新后的行本身，如下所示(您可以像使用'*'一样选择整个行，或者指定您想要的字段)：

console.log('resultFromQuery', resultFromQuery[0]);