我使用Sqlite作为数据库和节点。js和Sequelize作为我项目的后端。我有以下型号:
message.model.js:
const {Sequelize} = require('sequelize');
const messageSchema = {
message_Id: { type: Sequelize.STRING, allowNull: false, primaryKey: true,unique: true },
msg_serial: { type: Sequelize.STRING, allowNull: false },
sender_user_id: { type: Sequelize.INTEGER, allowNull: false },
target_user_id: { type: Sequelize.INTEGER, allowNull: false },
message_detail: { type: Sequelize.TEXT },
delivered: { type: Sequelize.BOOLEAN, allowNull: false },
is_seen: { type: Sequelize.BOOLEAN, allowNull: false }
};
module.exports = {messageSchema}
request.model.js:
const { Sequelize } = require('sequelize');
const requestSchema = {
request_id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
sender_user_id: { type: Sequelize.INTEGER, allowNull: false },
target_user_id: { type: Sequelize.INTEGER, allowNull: false },
is_seen: { type: Sequelize.BOOLEAN, default: false },
is_accepted: { type: Sequelize.BOOLEAN, default: false }
}
module.exports = { requestSchema };
role.model.js:
const { Sequelize } = require('sequelize');
const roleSchema = [{
role_id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
role_name: { type: Sequelize.STRING, allowNull: false }
}, { timestamps: false }]
module.exports = { roleSchema }
user.model.js:
const {Sequelize} = require('sequelize');
const userSchema = {
user_id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
username: { type: Sequelize.STRING, allowNull: false, unique: true },
name: { type: Sequelize.STRING, allowNull: false },
password: { type: Sequelize.STRING, allowNull: false },
last_update: { type: Sequelize.DATE, allowNull: true },
profile_image: { type: Sequelize.STRING, allowNull: true },
lastVisitAt: { type: Sequelize.DATE, allowNull: true },
is_locked: { type: Sequelize.BOOLEAN, allowNull: true },
lock_until: { type: Sequelize.DATE, allowNull: true },
is_banned: { type: Sequelize.BOOLEAN, allowNull: true },
createdAt: { type: Sequelize.DATE, allowNull: false },
updatedAt: { type: Sequelize.DATE, allowNull: true },
socket_id: { type: Sequelize.INTEGER, allowNull: true },
roleRef: { type: Sequelize.INTEGER, allowNull: false }
}
module.exports = {userSchema}
context.js:
const { Sequelize } = require('sequelize');
const { userSchema } = require('./user.model');
const { messageSchema } = require('./message.model');
const { roleSchema } = require('./role.model');
const { requestSchema } = require('./request.model');
var connection;
(async function () {
if (!connection) {
//Initialize connection on the first run
connection = new Sequelize({
dialect: 'sqlite',
storage: 'LinkChat.sqlite'
});
try {
await connection.authenticate();
console.log("Database Connection has been established successfully.")
} catch (error) {
console.log("Unable to connect to the database.", error);
}
}
CreateTables();
})();
async function CreateTables() {
//Create the tables
try {
await connection.sync({ force: true }).then(() => {
console.log("Created the tables successfully.");
});
} catch (error) {
console.log("Cannot create the tables.\n" + error);
}
initialRole();
initialUser();
}
const User = connection.define("Users", userSchema);
const Role = connection.define("Roles", roleSchema[0], roleSchema[1]);
const Message = connection.define("Messages", messageSchema);
const Request = connection.define("Requests", requestSchema);
User.belongsTo(Role, {
foreignKey: "roleRef",
targetKey: "role_id"
});
User.hasMany(Message, { as: "Messages" });
Message.belongsTo(User, {
foreignKey: "sender_user_id",
targetKey: "user_id"
});
Message.belongsTo(User, {
foreignKey: "target_user_id",
targetKey: "user_id"
});
User.hasMany(Request, { as: "Requests" });
Request.belongsTo(User, {
foreignKey: "sender_user_id",
targetKey: "user_id"
});
Request.belongsTo(User, {
foreignKey: "target_user_id",
targetKey: "user_id"
});
function initialRole() {
Role.create({
role_name: 'developer'
});
Role.create({
role_name: 'admin'
});
Role.create({
role_name: 'user'
});
}
module.exports = { connection, Sequelize, models: { userTable: User, roleTable: Role, messageTable: Message, requestTable: Request } }
问题是,在创建表时,Messages
和Requests
表中有两个不需要的UserUserId
列。如何解决此问题?
1条答案
按热度按时间t1rydlwq1#
您只需要为所有配对关联(例如belognsTo/asMany)指定相同的
foreignKey
选项,并在将相同的表配对两次的情况下使用不同的别名:另外,如果
targetKey
的值指向PK字段,则无需指示targetKey
。查看我的其他answer to the similar question