如何将此SQL转换为压缩

Question

我需要通过这样的两个字段连接另一个表，如何通过压缩

获得这个

SELECT
* 
FROM
Issue
LEFT OUTER JOIN File1 ON File1.project_id = Issue.project_id 
AND File1.file_path = Issue.file_path 
WHERE
Issue.project_id = 1 
AND version_id =1

所以我试着用这个

class Issue extends Model<attributes, creationAttributes> {
...
@BelongsTo(() => File1, {
foreignKey: "project_id",
targetKey: "projectId",
constraints: false,
})
file!: File1;
}

Issue.findAll({
where:{
projectId:1,
versionId:1
},
include:[File1]
})

但是这段代码转换成SQL是

SELECT
* 
FROM
Issue
LEFT OUTER JOIN File1 ON File1.project_id = Issue.project_id 
WHERE
Issue.project_id = 1 
AND version_id =1

不是I want to get

Answer 1

你可以试试

Issue.findAll({
where:{
projectId:1,
versionId:1
},
include:[{
model: File1,
as: 'File1',
where: {
file_path: '$Issue.file_path$',
}
}]
})

或者你可以参考这个文档:https://sequelize.org/docs/v6/advanced-association-concepts/eager-loading/#eager-loading-filtered-at-the-associated-model-level

Answer 2

受@SayedKhaidirAli的回答和文档的启发，这是有用的

Issue.findAll({
where:{
projectId:1,
versionId:1
},
include:[{
model: File1,
where: {
file_path: { [Op.eq]: Sequelize.col("Issues.file_path") },
}
}]})