sequelize.js - 在 hasMany 关联上使用 set 时出错

Question

我有一个 Invoice 模型，它可以与多个 LineItem 模型相关联。当我调用 setLineItems(lineItems, {transaction: t});(其中 lineItems 是一个 LineItem 对象数组)时，由于 Sequelize 在 scnenes 后面创建了一些非常奇怪的 SQL，我收到一个错误:

DatabaseError [SequelizeDatabaseError]: invalid input syntax for integer: "[object Object]"
// Stack trace cut out
{
    name: 'error',
    length: 108,
    severity: 'ERROR',
    code: '22P02',
    detail: undefined,
    hint: undefined,
    position: '69',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'numutils.c',
    line: '62',
    routine: 'pg_atoi',
    sql: `UPDATE "LineItems" SET "invoiceId"=$1,"updatedAt"=$2 WHERE "id" IN ('[object Object]', '[object Object]')`,
    parameters: [ 8, '2020-04-25 06:56:19.482 +00:00' ]
}

我的代码/定义如下:

发票.js:

'use strict';
module.exports = (sequelize, DataTypes) => {
    const Invoice = sequelize.define('Invoice', {
        // various fields, including a numeric id
    }, {});
    Invoice.associate = function (models) {
        Invoice.hasMany(models.LineItem, { foreignKey: 'invoiceId' });
    };
    return Invoice;
};

lineItems.js:

'use strict';
module.exports = (sequelize, DataTypes) => {
    const LineItem = sequelize.define('LineItem', {
        id: {
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
            type: DataTypes.INTEGER
        },
        invoiceId: {
            type: DataTypes.INTEGER,
            references: {
                model: 'Invoices',
                key: 'id'
            },
        },
        hsnSacCode: DataTypes.STRING(30),
        desc: DataTypes.STRING(1500),
        qty: DataTypes.DECIMAL(10, 3),
        rate: DataTypes.DECIMAL(10, 3),
        amt: DataTypes.DECIMAL(16, 3)
    }, {});
    LineItem.associate = function (models) {
    };
    return LineItem;
};

Invoice 迁移:

'use strict';

const tbl = 'Invoices';

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable(tbl, {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            // other fields
        });
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable('Invoices');
    }
};

LineItem 迁移:

'use strict';
module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable('LineItems', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            invoiceId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'Invoices',
                    key: 'id'
                },
                onUpdate: 'CASCADE',
                onDelete: 'SET NULL'
            },
            hsnSacCode: {
                type: Sequelize.STRING(30)
            },
            desc: {
                type: Sequelize.STRING(1500)
            },
            qty: {
                type: Sequelize.DECIMAL(10,3)
            },
            rate: {
                type: Sequelize.DECIMAL(10,3)
            },
            amt: {
                type: Sequelize.DECIMAL(16,3)
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE
            },
            updatedAt: {
                allowNull: false,
                type: Sequelize.DATE
            }
        });
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable('LineItems');
    }
};

奇怪的是，生成的SQL竟然这么错误!!样本:

START TRANSACTION;
INSERT INTO "Invoices" ("id","invoiceNum","vendorId","corporateId","dated","currency","preTaxAmt","igstPct","sgstPct","totalAmt","tdsPaid","gstInputCreditClaimed","status","vendorStatus","type","createdAt","updatedAt") VALUES (DEFAULT,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16) RETURNING *;
SELECT "id", "invoiceId", "hsnSacCode", "desc", "qty", "rate", "amt", "createdAt", "updatedAt" FROM "LineItems" AS "LineItem" WHERE "LineItem"."invoiceId" = 8;
UPDATE "LineItems" SET "invoiceId"=$1,"updatedAt"=$2 WHERE "id" IN ('[object Object]', '[object Object]')
ROLLBACK;

这是执行的代码:

const dbInvoice = await sequelize.transaction(async (t) => {
    const invoice = _.omit(input, ['lineItems']);
    const { lineItems } = _.pick(input, ['lineItems']);
    const dbInvoice = await Invoice.create(invoice, { transaction: t });
    console.log(`lineItems:`, lineItems);
    await dbInvoice.setLineItems(lineItems, {transaction: t});
    return Promise.resolve(dbInvoice.dataValues);
});

console.log 在我尝试保存之前的 lineItems 输出:

lineItems: [
  {
    hsnSacCode: 'arbit',
    desc: 'Daycare for Child1, Child2',
    qty: 2,
    rate: '2000.00',
    amt: '4000.00'
  },
  {
    hsnSacCode: 'arbit',
    desc: 'Daycare for Child1, Child2',
    qty: 1,
    rate: '2000.00',
    amt: '2000.00'
  }
]

如果我用刚刚保存的 lineItem 的 id 填充每个 Invoice，然后正常保存它们(即，不使用 setLineItems(...) )，它会按预期工作。

使用 PostgresQL RDBMS。

Sequelize :[节点:12.14.1，CLI:5.5.1，ORM:5.21.3

Answer 1

您正在使用原始对象(更重要的是，它们是新对象)调用 setItems。您需要调用 addItems 然后 Sequelize 将插入它们并为您关联它们。

我希望 setItems 很聪明并为您创建了项目。似乎即使项目存在于数据库中，您也只能使用空数组或 Sequelize 对象数组(不是使用 setX 字段手动创建或从浏览器发回的对象等)调用 id 关联方法.

我怎么知道该调用哪一位？

最好的方法是先保存项目，然后再将它们添加到发票。在代码中松散地说:

// Save the items first.
let savedItems = [];
for(const item of items) {
  let savedItem;
  if(item.id) {
    savedItem = await sequelize.Item.save(item);
  } else {
    savedItem = await sequelize.Item.create(item);
  }
  savedItems.push(savedItem);
}

// Now this will work.
await Invoice.setItems(savedItems);