node.js - Sequelize 中的内部连接查询引用多个表

Question

我如何在 SEQUELIZE 中写下查询？

select vdc_id,vdc.name as vdc,
districts.name as district,
states.name as state,
countries.name as country
from vdc
INNER JOIN districts on (vdc.district_id=districts.district_id and vdc.name like 'L%')
INNER JOIN states on (districts.state_id=states.state_id) 
INNER JOIN countries on (states.country_id=countries.country_id)
order by district asc;

关联:

vdc 表包含 fk_district_id

db.vdc.belongsTo(db.districts,{
  foreignKey:"district_id"
})
db.districts.hasMany(db.vdc,{
  foreignKey:"district_id"
})

地区表包含 fk_state_id

db.districts.belongsTo(db.states,{
  foreignKey:"state_id"
})
db.states.hasMany(db.districts,{
  foreignKey:"state_id"
})

状态表包含 fk_country_id

db.states.belongsTo(db.countries,{
  foreignKey:"country_id"
})
db.countries.hasMany(db.states,{
  foreignKey:"country_id"
})

感谢您!

Answer 1

看来您需要类似下面的代码。您需要调整它以使用您的模型，因为它们没有提供，但这应该让您了解获得所需查询所需的内容。

vdc.findAll({
    attributes: ['vcd_id', 'name'],
    include: [
        {
            model: 'districts', // Or districts model
            attributes: [['name', 'district']], // name as district
            required: true, // Use inner join instead of left join
            on: { // Join on district id and name like 'L%'
                'district_id': { [Op.eq]: sequelize.col('districts.district_id') },
                'name': { [Op.like]: 'L%' }
            }
        },
        // Custom join shouldn't be necessary for the following due to foreign key config
        {
            model: 'states',
            attributes: [['name', 'state']],
            required: true
        },
        {
            model: 'countries',
            attributes: [['name', 'country']],
            required: true
        }
    ],
    order: [['district', 'ASC']]
});