mysql - 使用belongsToMany : Unknown column error 对查询进行 Sequelize

Question

我的 MySQL 表:
表 posts :

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| short_title | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

表 authors :

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| last_name  | varchar(100) | NO   |     | NULL    |                |
| first_name | varchar(100) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

直通表 posts_authors :

+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| post_id   | int  | NO   | PRI | NULL    |       |
| author_id | int  | NO   | PRI | NULL    |       |
+-----------+------+------+-----+---------+-------+

Sequelize 模型:

const Post = sequelize.define('Post', {
    shortTitle: {
        field: 'short_title',
        type: DataTypes.STRING(255)
    }
}, {
    tableName: 'posts',
    timestamps: false
});

const Author = sequelize.define('Author', {
    firstName: {
        field: 'first_name',
        type: DataTypes.STRING(100)
    },
    lastName: {
        field: 'last_name',
        type: DataTypes.STRING(100)
    }
}, {
    tableName: 'authors',
    timestamps: false
});

const PostsAuthors = sequelize.define('PostsAuthors', {
    postId: {
        field: 'post_id',
        type: DataTypes.INTEGER
    },
    authorId: {
        field: 'author_id',
        type: DataTypes.INTEGER
    }
}, {
    tableName: 'posts_authors',
    timestamps: false
});

Author.belongsToMany(Post, { through: PostsAuthors });
Post.belongsToMany(Author, { through: PostsAuthors });

当我尝试查询包含作者的帖子时...:

const post1 = await Post.findOne({
    where: { id: 1 },
    include: Author
});

...我收到一个错误:

Unknown column 'Authors->PostsAuthors.AuthorId' in 'field list'

生成的 SQL 如下所示:

SELECT
  `Post`.`id`,
  `Post`.`short_title` AS `shortTitle`,
  `Authors`.`id` AS `Authors.id`,
  `Authors`.`first_name` AS `Authors.firstName`,
  `Authors`.`last_name` AS `Authors.lastName`,
  `Authors->PostsAuthors`.`post_id` AS `Authors.PostsAuthors.postId`,
  `Authors->PostsAuthors`.`author_id` AS `Authors.PostsAuthors.authorId`,
  `Authors->PostsAuthors`.`AuthorId` AS `Authors.PostsAuthors.AuthorId`,
  `Authors->PostsAuthors`.`PostId` AS `Authors.PostsAuthors.PostId`
FROM `posts` AS `Post` 
  LEFT OUTER JOIN ( `posts_authors` AS `Authors->PostsAuthors` 
    INNER JOIN `authors` AS `Authors` 
      ON `Authors`.`id` = `Authors->PostsAuthors`.`AuthorId`)
    ON `Post`.`id` = `Authors->PostsAuthors`.`PostId`
WHERE `Post`.`id` = 1;

我如何告诉 Sequelize AuthorId 列被称为 author_id ？这不是在模型中定义列时 field 属性的用途吗？

Answer 1

我发现，这就是 foreignKey 属性的用途:

Author.belongsToMany(Post, { through: PostsAuthors, foreignKey: 'author_id' });
Post.belongsToMany(Author, { through: PostsAuthors, foreignKey: 'post_id' });