mysql - Sequelize 错误 > "name": "SequelizeEagerLoadingError"

Question

我有两个表:

//User.js

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define("User", {
    userId: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    email: {
      type: DataTypes.STRING,
    },
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    lastName: {
      type: DataTypes.STRING,
      defaultValue: "",
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    chapterId: {
      type: DataTypes.STRING,
    },
  });

  User.associate = (models) => {
    User.belongsTo(models.Chapter, {
      foreignKey: "chapterId",
      targetKey: "chapterId",
      as: "chapter",
    });
  };

  return User;
};

和

//chapter table

module.exports = (sequelize, DataTypes) => {
  const Chapter = sequelize.define("Chapter", {
    chapterId: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    chapterName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    isChapterLocal: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
    },
  });

  Chapter.associate = (models) => {
  };

  return Chapter;
};

我正在尝试获取包含章节的用户。

let getAll = async (req, res) => {
  try {
    const userData = await db.User.findAll({
      include: [
        {
          model: Chapter,
          as: "chapter",
        },
      ],
    });
    res.send(userData);
  } catch (e) {
    res.send(e);
  }
};

如何从章节表中包含章节 ID 和章节名称，如用户表的 ChapterId 行中所示。
我是 sequelize 和 MySQL 的新手，不确定我在用户模型中定义的关系是否良好。
我们是否需要在两个表中定义关联。

Answer 1

它应该按预期工作。例如。

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

class User extends Model {}
User.init(
  {
    userId: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    email: {
      type: DataTypes.STRING,
    },
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    lastName: {
      type: DataTypes.STRING,
      defaultValue: '',
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    chapterId: {
      type: DataTypes.STRING,
    },
  },
  { sequelize, modelName: 'User' },
);

class Chapter extends Model {}
Chapter.init(
  {
    chapterId: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    chapterName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    isChapterLocal: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
    },
  },
  { sequelize, modelName: 'Chapter' },
);

User.belongsTo(Chapter, { foreignKey: 'chapterId', targetKey: 'chapterId', as: 'chapter' });

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await User.create(
      {
        userId: '1',
        email: 'example@gmail.com',
        firstName: 'Lin',
        lastName: 'Du',
        password: '123',
        chapter: {
          chapterId: '1',
          chapterName: 'ok',
          isChapterLocal: false,
        },
      },
      { include: [{ model: Chapter, as: 'chapter' }] },
    );
    // test
    const userData = await User.findAll({
      include: [{ model: Chapter, as: 'chapter' }],
      raw: true,
    });
    console.log('userData:', userData);
  } catch (error) {
    console.log(error);
  } finally {
    sequelize.close();
  }
})();

执行结果:

Executing (default): DROP TABLE IF EXISTS "User" CASCADE;
Executing (default): DROP TABLE IF EXISTS "Chapter" CASCADE;
Executing (default): DROP TABLE IF EXISTS "Chapter" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "Chapter" ("chapterId" VARCHAR(255) NOT NULL UNIQUE , "chapterName" VARCHAR(255) NOT NULL, "isChapterLocal" BOOLEAN NOT NULL, PRIMARY KEY ("chapterId"));
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 = 'Chapter' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "User" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "User" ("userId" VARCHAR(255) NOT NULL UNIQUE , "email" VARCHAR(255), "firstName" VARCHAR(255) NOT NULL, "lastName" VARCHAR(255) DEFAULT '', "password" VARCHAR(255) NOT NULL, "chapterId" VARCHAR(255) REFERENCES "Chapter" ("chapterId") ON DELETE NO ACTION ON UPDATE CASCADE, PRIMARY KEY ("userId"));
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 = 'User' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "Chapter" ("chapterId","chapterName","isChapterLocal") VALUES ($1,$2,$3) RETURNING *;
Executing (default): INSERT INTO "User" ("userId","email","firstName","lastName","password","chapterId") VALUES ($1,$2,$3,$4,$5,$6) RETURNING *;
Executing (default): SELECT "User"."userId", "User"."email", "User"."firstName", "User"."lastName", "User"."password", "User"."chapterId", "chapter"."chapterId" AS "chapter.chapterId", "chapter"."chapterName" AS "chapter.chapterName", "chapter"."isChapterLocal" AS "chapter.isChapterLocal" FROM "User" AS "User" LEFT OUTER JOIN "Chapter" AS "chapter" ON "User"."chapterId" = "chapter"."chapterId";
userData: [ { userId: '1',
    email: 'example@gmail.com',
    firstName: 'Lin',
    lastName: 'Du',
    password: '123',
    chapterId: '1',
    'chapter.chapterId': '1',
    'chapter.chapterName': 'ok',
    'chapter.isChapterLocal': false } ]

检查数据库:

node-sequelize-examples=# select * from "User";
 userId |       email       | firstName | lastName | password | chapterId
--------+-------------------+-----------+----------+----------+-----------
 1      | example@gmail.com | Lin       | Du       | 123      | 1
(1 row)

node-sequelize-examples=# select * from "Chapter";
 chapterId | chapterName | isChapterLocal
-----------+-------------+----------------
 1         | ok          | f
(1 row)