sqlite 在使用Sequelize创建一对多关联时创建不需要的列

q0qdq0h2  于 2023-04-30  发布在  SQLite
关注(0)|答案(1)|浏览(377)

我使用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 } }

问题是,在创建表时,MessagesRequests表中有两个不需要的UserUserId列。如何解决此问题?

t1rydlwq

t1rydlwq1#

您只需要为所有配对关联(例如belognsTo/asMany)指定相同的foreignKey选项,并在将相同的表配对两次的情况下使用不同的别名:

User.hasMany(Message, { as: "SentMessages", foreignKey: "sender_user_id" });
User.hasMany(Message, { as: "ReceivedMessages", foreignKey: "target_user_id" });

Message.belongsTo(User, {
    foreignKey: "sender_user_id",
    as: 'Sender'
});

Message.belongsTo(User, {
    foreignKey: "target_user_id",
    as: 'Receiver'
});

User.hasMany(Request, { as: "SentRequests", foreignKey: "sender_user_id" });
User.hasMany(Request, { as: "ReceivedRequests", foreignKey: "target_user_id" });

Request.belongsTo(User, {
    foreignKey: "sender_user_id"
});

Request.belongsTo(User, {
    foreignKey: "target_user_id"
});

另外,如果targetKey的值指向PK字段,则无需指示targetKey
查看我的其他answer to the similar question

相关问题