javascript - 从嵌套对象创建时对设置外键进行 Sequelize

Question

我使用 PostgreSQL 作为我的数据库引擎。
我定义了两个模型，RateSection 和 Shop，其中一个 Shop 有许多 RateSection，每个 RateSection 属于一个 Shop

export const Shop = <ShopModelStatic>db.define(
    'shop',
    {
        shopUrl: {
            type: sequelize.STRING,
            allowNull: false
        },
        active: {
            type: sequelize.BOOLEAN,
            allowNull: false
        }
    },
    {
        underscored: true
    }
);

export const RateSection = <RateSectionModelStatic>db.define(
    'rate_section',
    {
        minWeight: {
            type: sequelize.FLOAT,
            allowNull: false
        },
        maxWeight: {
            type: sequelize.FLOAT,
            allowNull: false
        },
        price: {
            type: sequelize.FLOAT,
            allowNull: false
        },
        freeShippingMinPrice: {
            type: sequelize.FLOAT
        },
        minShippingDays: {
            type: sequelize.INTEGER
        },
        maxShippingDays: {
            type: sequelize.INTEGER
        },
    },
    {
        underscored: true
    }
);

Shop.hasMany(RateSection);
RateSection.belongsTo(Shop);

它们在数据库表中正确定义。
目前，当我想插入一个新的具有对应关系的 RateSection 时，我被迫这样做

RateSection.create(rateSection).then((newRateSection: any) => {
                newRateSection.setShop(shop);
                return newRateSection.save();
            });

我对这个方法的主要担忧是它对每个 RateSection.create 进行多次调用。
我希望能够像这样将嵌套对象传递给 RateSection.create

const rateSection {
  minWeight: 1,
  maxWeight: 2,
  ...whateverprops,
  shop: {
    id: 1,
  }
}

当我传递这个嵌套对象时，它会创建 rate 部分，但外键设置为 null
我试过这个没有运气

RateSection.create(rateSection, { include: [Shop] });

你能帮个弟弟吗？

Answer 1

它应该工作。这是一个工作示例:
index.ts :

import { sequelize as db } from '../../db';
import sequelize, { Model, BuildOptions } from 'sequelize';

interface ShopModel extends Model {}
type ShopModelStatic = typeof Model & {
  new (values?: object, options?: BuildOptions): ShopModel;
};

interface RateSectionModel extends Model {}
type RateSectionModelStatic = typeof Model & {
  new (values?: object, options?: BuildOptions): RateSectionModel;
};

export const Shop = <ShopModelStatic>db.define(
  'shop',
  {
    shopUrl: {
      type: sequelize.STRING,
      allowNull: false,
    },
    active: {
      type: sequelize.BOOLEAN,
      allowNull: false,
    },
  },
  {
    underscored: true,
  },
);

export const RateSection = <RateSectionModelStatic>db.define(
  'rate_section',
  {
    minWeight: {
      type: sequelize.FLOAT,
      allowNull: false,
    },
    maxWeight: {
      type: sequelize.FLOAT,
      allowNull: false,
    },
    price: {
      type: sequelize.FLOAT,
      allowNull: false,
    },
    freeShippingMinPrice: {
      type: sequelize.FLOAT,
    },
    minShippingDays: {
      type: sequelize.INTEGER,
    },
    maxShippingDays: {
      type: sequelize.INTEGER,
    },
  },
  {
    underscored: true,
  },
);

Shop.hasMany(RateSection);
RateSection.belongsTo(Shop);

(async function test() {
  try {
    await db.sync({ force: true });
    const rateSection = {
      minWeight: 1,
      maxWeight: 2,
      price: 100,
      shop: {
        id: 1,
        shopUrl: 'https://stackoverflow.com/',
        active: true,
      },
    };
    await RateSection.create(rateSection, { include: [Shop] });
  } catch (error) {
    console.log(error);
  } finally {
    await db.close();
  }
})();

执行上述代码后，查看数据库中的数据记录:

node-sequelize-examples=# select * from "shop";
 id |          shop_url          | active
----+----------------------------+--------
  1 | https://stackoverflow.com/ | t
(1 row)

node-sequelize-examples=# select * from "rate_section";
 id | min_weight | max_weight | price | free_shipping_min_price | min_shipping_days | max_shipping_days | shop_id
----+------------+------------+-------+-------------------------+-------------------+-------------------+---------
  1 |          1 |          2 |   100 |                         |                   |                   |       1
(1 row)

外键 shop_id 按预期添加到 rate_section 中。代码的执行调试信息:

{ POSTGRES_HOST: '127.0.0.1',
  POSTGRES_PORT: '5430',
  POSTGRES_PASSWORD: 'testpass',
  POSTGRES_USER: 'testuser',
  POSTGRES_DB: 'node-sequelize-examples' }
Executing (default): DROP TABLE IF EXISTS "rate_section" CASCADE;
Executing (default): DROP TABLE IF EXISTS "shop" CASCADE;
Executing (default): DROP TABLE IF EXISTS "shop" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "shop" ("id"   SERIAL , "shop_url" VARCHAR(255) NOT NULL, "active" BOOLEAN NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'shop' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "rate_section" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "rate_section" ("id"   SERIAL , "min_weight" FLOAT NOT NULL, "max_weight" FLOAT NOT NULL, "price" FLOAT NOT NULL, "free_shipping_min_price" FLOAT, "min_shipping_days" INTEGER, "max_shipping_days" INTEGER, "shop_id" INTEGER REFERENCES "shop" ("id") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'rate_section' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "shop" ("id","shop_url","active") VALUES ($1,$2,$3) RETURNING *;
Executing (default): INSERT INTO "rate_section" ("id","min_weight","max_weight","price","shop_id") VALUES (DEFAULT,$1,$2,$3,$4) RETURNING *;

源代码: https://github.com/mrdulin/node-sequelize-examples/tree/master/src/examples/stackoverflow/60196739