如何在Sequelize中为Offers表设置两个指向Customs表的外键

我有一个Offers表，其中包含exit_customs和destination_customs两个字段，这两个字段存储的是Customs表的ID。现在需要在Sequelize中为这两个字段设置指向Customs表的外键。以下是我的列表查询函数：

router.post('/api/logistic/offers/get-offers', async (req, res) => {
    const {limit, page, sortColumn, sortType, search} = req.body;
    const total = await Offers.findAll();
    const offersList = await Offers.findAll({
        limit: limit,
        offset: (page - 1) * limit,
        order: [
            [sortColumn, sortType]
        ],
        where: {
            [Op.or]:[
                {
                    offers_no: {
                        [Op.substring]: [
                            search
                        ]
                    }
                },
                {
                    agreement_date: {
                        [Op.substring]: [
                            search
                        ]
                    }
                },
                {
                    routes: {
                        [Op.substring]: [
                            search
                        ]
                    }
                },
                {
                    type_of_the_transport: {
                        [Op.substring]: [
                            search
                        ]
                    }
                },
            ]
        }
    });
    res.json({
        total: total.length,
        data: offersList
    });
})

实现步骤 #

1. 在Offers模型中定义外键约束和关联关系

在Offers模型初始化时，给两个外键字段添加约束，并建立与Customs表的关联。注意必须给两个关联设置不同的别名，避免冲突：

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../path-to-your-sequelize-instance'); // 替换为你的Sequelize实例路径
const Customs = require('./Customs'); // 引入Customs模型

class Offers extends Model {}

Offers.init({
    // 业务字段
    offers_no: DataTypes.STRING,
    agreement_date: DataTypes.DATE,
    routes: DataTypes.STRING,
    type_of_the_transport: DataTypes.STRING,
    // 出境海关外键
    exit_customs: {
        type: DataTypes.INTEGER,
        references: {
            model: Customs,
            key: 'id' // 对应Customs表的主键字段
        },
        allowNull: false // 根据业务需求调整是否允许为空
    },
    // 目的海关外键
    destination_customs: {
        type: DataTypes.INTEGER,
        references: {
            model: Customs,
            key: 'id'
        },
        allowNull: false
    }
}, {
    sequelize,
    modelName: 'Offers'
});

// 建立关联，用别名区分两个不同的外键关联
Offers.belongsTo(Customs, { 
    as: 'exitCustoms', 
    foreignKey: 'exit_customs'
});
Offers.belongsTo(Customs, { 
    as: 'destinationCustoms', 
    foreignKey: 'destination_customs'
});

module.exports = Offers;

2. 优化查询函数（可选）

如果需要在查询Offers时同时获取关联的海关信息，可以在findAll中加入include选项。另外，用count方法统计总数比findAll().length性能更优：

router.post('/api/logistic/offers/get-offers', async (req, res) => {
    const {limit, page, sortColumn, sortType, search} = req.body;
    
    // 直接统计符合条件的数量，无需查询全量数据
    const total = await Offers.count({
        where: {
            [Op.or]: [
                { offers_no: { [Op.substring]: search } },
                { agreement_date: { [Op.substring]: search } },
                { routes: { [Op.substring]: search } },
                { type_of_the_transport: { [Op.substring]: search } }
            ]
        }
    });

    const offersList = await Offers.findAll({
        limit: limit,
        offset: (page - 1) * limit,
        order: [[sortColumn, sortType]],
        where: {
            [Op.or]: [
                { offers_no: { [Op.substring]: search } },
                { agreement_date: { [Op.substring]: search } },
                { routes: { [Op.substring]: search } },
                { type_of_the_transport: { [Op.substring]: search } }
            ]
        },
        // 关联查询海关信息，指定需要返回的字段
        include: [
            {
                model: Customs,
                as: 'exitCustoms',
                attributes: ['id', 'name'] // 根据实际需求选择字段
            },
            {
                model: Customs,
                as: 'destinationCustoms',
                attributes: ['id', 'name']
            }
        ]
    });

    res.json({
        total: total,
        data: offersList
    });
})

内容的提问来源于stack exchange，提问作者Ubeydullah Yılmaz