node.js - sequelize 获取引用表的列值

Question

我有三张桌子会议、房间和项目

meetings.belongsTo(models.rooms, { foreignKey: "room_id" , targetKey: "id"});
rooms.hasMany(models.meetings, { foreignKey : "id", targetKey : "room_id"});
rooms.belongsTo(models.projects, { foreignKey: "project_id", targetKey: "id"});
projects.hasMany(models.rooms,  {foreignKey:"id", targetKey:"project_id"});

id、room_id 和 project_id 是它们各自表的主键。我想找到特定会议 ID 的某些项目列的价值。如何使用 sequelize nodejs 编写单个查询来执行此操作？下面的查询需要使用给出正确结果的 sequelize 执行

select project_meta from projects  p
inner join rooms  r on p.id = r.project_id
inner join  meetings  m on  r.id = m.room_id
where  m.id = "QBZJ0TK7V6NFSPPWGFNCN";

写了以下

projects.findAll({
    include: [{
        model: rooms,
        include: [{
            model: meetings,
            where: {
                id: "QBZJ0TK7V6NFSPPWGFNCN"
            }
        }]
    }],
    attributes: ['project_meta']
}

但它正在执行不同的查询并给出意外的结果

关联有问题吗？

标签： node.jsormsequelize.js

Answer 1

您必须使用 SQL 连接（sequelize 的包含）

function myQuery(meetingId) {
    let options = {};
    options.attributes = ['column1', 'column2', ...]; // The specific arrtibutes you need.
    options.include = [
        {
            model: RoomModel,
            include: [
                {
                    model: MeetingModel,
                    where: {
                        id: meetingId  // The specific meeting id
                    }
                }
            ]
        }
    ];
    ProjectModel.findAll(options);
}