sequelize nodejs Inner-join with 3 tables？

3htmauhk 于 2023-08-04 发布在 Node.js

关注(0)|答案(2)|浏览(90)

这是我的原生查询，它包含2个内部连接。我可以一个接一个地写这个。但是我想从nodejs Sequelize写这个查询。

SELECT * 
FROM video_refs r
JOIN accepts a ON a.ComplaintId = r.complaint_id
JOIN vehicles v ON v.acceptId = a.id
WHERE v.vehicleNumber = 'BG345'

字符串
我尝试过，但此Video_Ref部分不工作

const foundVehicleList =await Vehicle.findAll({
                where: {
                    vehicleNumber:'BG1234',
            
                },
                include: [                                               
                      { model: Accept, as: 'Accept', attributes: []},   
                      { model: Video_Ref, as: 'Video_Ref', attributes: []},   
                ],
                attributes: [
                    
                  [Sequelize.literal('Accept.ComplaintId'),'ComplaintId']
                ]
    
            });

型
这些是关系

db.Complaint.hasOne(db.Video_Ref,{foreignKey: 'complaint_id', sourceKey: 'id'});
db.Video_Ref.belongsTo(db.Complaint,{foreignKey: 'complaint_id', targetKey: 'id'});

db.Complaint.hasOne(db.Accept,{foreignKey: 'ComplaintId ', sourceKey: 'id'});
db.Accept.belongsTo(db.Complaint,{foreignKey: 'ComplaintId ', targetKey: 'id'});

db.Accept.hasMany(db.Vehicle, {foreignKey: 'acceptId', sourceKey: 'id'});
db.Vehicle.belongsTo(db.Accept, {foreignKey: 'acceptId', sourceKey: 'id'});

db.Complaint.hasOne(db.Accept,{foreignKey: 'ComplaintId', sourceKey: 'id'});
db.Accept.belongsTo(db.Complaint,{foreignKey: 'ComplaintId ', targetKey: 'id'});

型

node.js

来源：https://stackoverflow.com/questions/69151606/sequelize-nodejs-inner-join-with-3-tables

2条答案

按热度按时间

ss2ws0br1#

我找到了一个答案

SELECT vf.id,vf.reference FROM video_refs vf INNER JOIN complaints c ON vf.`complaint_id` = c.`id` INNER JOIN Accepts a ON c.`id` = a.`ComplaintId` INNER JOIN vehicles vh ON a.`id` = vh.`acceptId` AND vh.`vehicleNumber` = 'BG1234' AND vh.`createdAt` >= '2021-09-06 09:11:38'

const VideoRefList=await Video_Ref.findAll({
            include : [
                {
                    model: Complaint,
                    required: true,
                    attributes: [],
                    include: [
                        {
                            model: Accept,
                            required: true,
                            attributes: [],
                            include : [
                                {
                                    model: Vehicle,
                                    required: true,
                                    attributes: [],
                                    where: {
                                        vehicleNumber:'BG1234',
                                        createdAt: {
                                            [Op.gte]: moment().subtract(7, 'days').toDate()
                                        }
                                    },
                                }
                            ]
                        }
                    ]
                }
            ],
                attributes: [
                    'id',
                    'reference'
                ]

        });

字符串

赞(0）回复(0）举报 2023-08-04

mo49yndu2#

这是一个如何使用3个表完成sequelize nodejs Inner-join的示例。我使用用户和团队成员表加入团队
''' const members = teams.findAll（{ include：[ { model：user，as：“owner”}，{ model：team_member，作为：“成员”，包括：[user] }，]，}）;
'''

赞(0）回复(0）举报 2023-08-04

我来回答

sequelize nodejs Inner-join with 3 tables？

2条答案

相关问题

热门标签

最新问答