postgresql - Sequelize 关联外键

Question

我想要与 sequelize db 的 INNER JOIN: postgress
我有 2 个数据库(用户和帖子)

用户

import Sequelize from "sequelize";
import { sequelize } from "../databases/database";
import Post from "./Post";
const User = sequelize.define(
    "user",
    {
        id: {
      type: Sequelize.INTEGER,
      autoIncrement: true,
      primaryKey: true
        },
        username: {
            type: Sequelize.STRING,

        },
        password: {
            type: Sequelize.STRING
        },
        image: {
            type: Sequelize.STRING
        }
    },
    { timestamps: false }
);
User.hasMany(Post, { foreignKey: "author", sourceKey: "username" });
Post.belongsTo(User, { foreignKey: "author", sourceKey: "username" });
export default User;

帖子

import Sequelize from "sequelize";
import { sequelize } from "../databases/database";

const Post = sequelize.define("post", {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true
  },
  title: {
    type: Sequelize.TEXT
  },
  content: {
    type: Sequelize.TEXT
  },
  image: {
    type: Sequelize.STRING
  },
  description: {
    type: Sequelize.TEXT
  },
  tags: {
    type: Sequelize.STRING
  },
  author: {
    type: Sequelize.STRING
  },
  createdAt: {
      field: 'createdat',
      type: Sequelize.DATE
  },
  updatedAt: {
    field: 'updatedat',
    type: Sequelize.DATE
  }
});

export default Post;

Controller

export const listAllPosts = async params => {
    const { offset } = params;

    try {
        const allPosts = await Post.findAll({
            // limit: 20,
            // offset: offset ? offset * 20 : 0,
            // order: [["id", "DESC"]],
            attributes: ["title", "content","tags","description","createdAt","updatedAt"],
            required: true,
            include:[
                {
                    model:User,
                    attributes: ["username", "image"],
                    required:true,
                }
            ]
        });
        return allPosts;
    } catch (error) {
        throw error;
    }
};

当我收集 api 显示错误时:

Executing (default): SELECT "post"."id", "post"."title", "post"."content", "post"."tags", "post"."description", "post"."createdat"AS "createdAt", "post"."updatedat" AS "updatedAt", "user"."id" AS "user.id", "user"."username" AS "user.username", "user"."image" AS "user.image" FROM "posts" AS "post" INNER JOIN "users" AS "user" ON "post"."author" = "user"."id"; error: SequelizeDatabaseError: operator does not exist: text = integer

但我想显示准确，我不想改变主键:

SELECT "post"."title", "post"."content", "post"."tags", "post"."description", "post"."createdat"AS "createdAt", "post"."updatedat" AS "updatedAt", "user"."username" AS "user.username", "user"."image" AS "user.image" FROM "posts" AS "post" INNER JOIN "users" AS "user" ON "post"."author" = "user"."username";

我怎么能这样做？

Answer 1

您最好使用“userId”然后使用“author”。它会解决你的问题。
并编辑关联

User.hasMany(Post, { foreignKey: "userId", sourceKey: "id" });
Post.belongsTo(User, { foreignKey: "id", sourceKey: "userId" });

并建议你阅读
https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/