两把洋钥匙一张桌子在亮片上

Question

我有Offers表2行exit_customs和destination_customs。这2行有一个我的海关表id。我的问题是如何两个(出口海关和目的地海关(外键一个(海关(表？

这是我的列表查询功能

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
    });
})

Answer 1

此解决方案根据需要将第一个表中的两个数据连接到另一个表的id。希望对有所帮助

Offers.belongsTo(Customs, {as: 'Exit_customs', foreignKey: 'exit_customs'});
Offers.belongsTo(Customs, {as: 'Destination_customs', foreignKey: 'destination_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
                        ]
                    }
                },
            ]
        },
        include: [{
            model: Customs,
            as: 'Exit_customs'
        }, {
            model: Customs,
            as: 'Destination_customs'
        }]
    });
    res.json({
        total: total.length,
        data: offersList
    });
})

Answer 2

您可以使用"作为">关键字，请在优惠型号文件中添加以下代码

static associate(models) {
      models.Offers.belongsTo(models.Customs, {
        foreignKey: "exit_customs",
        as: "exitCustomsDetails",
      });
      models.Offers.belongsTo(models.Customs, {
        foreignKey: "destination_customs",
        as: "destinationCustomsDetails",
      });
}