CODEWITHSUNDEEP
sqlnode.jsjoinsequelize.jsinner-join
Yasiru Ayeshmantha
Yasiru Ayeshmantha

Reputation: 101

sequelize nodejs Inner-join with 3 tables?

This is my native query it includes 2 inner joins. I can write this is one after another.But I want to write this query from 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'

I tried but this Video_Ref part not working

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

These are the relation

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

Upvotes: 0

Views: 6217

Answers (2)

Sirtaj Khan
Sirtaj Khan

Reputation: 1

This is an example of how to accomplish a sequelize nodejs Inner-join with 3 tables. I join team with the user and team member tables

''' const members = teams.findAll({ include: [ { model: user, as: "owner" }, { model: team_member, as: "members", include: [user] }, ], });

'''

Upvotes: 0

Yasiru Ayeshmantha
Yasiru Ayeshmantha

Reputation: 101

I found an answer like

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'
                ]

        });

Upvotes: 5

Related Questions