我希望INNER JOIN与sequelize db: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;
调节器
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显示错误时:
执行(默认):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”post“AS”post“INNER JOIN”users“AS”user“ON”post“ . ”author“=”user“ . ”id“;错误:SequelizeDatabaseError:运算符不存在:text = integer
但我想要显示确切,我不想更改primaryKey:
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";
我怎么能这样做?
1 回答
你应该更好地使用'userId'然后'author' . 它会解决你的问题 . 并编辑关联
建议你阅读https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/