Two foreign key one table in sequalize

user_19600004 0
Viewed 25

I have Offers table 2 row exit_customs and destination_customs. This 2 row has a my Customs table id. My problem is how to two(exit_customs and destination_customs) foreign key one(Customs) table?

Here is my list query function

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
    });
})
sequelize.js node.js javascript
2 Answers

this solution connects the two data in the first table to the id in the other table as you want. hope it helps

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

You can give foreign key using "as" keyword, please add below code in your Offers models file

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

      models.Offers.belongsTo(models.Customs, {
        foreignKey: "destination_customs",
        as: "destinationCustomsDetails",
      });
}
user_173166240
Related