快递js Sequelize查询-Java 学习之路

我在我的项目中使用MySQL数据库 . 问题考虑复杂的查询，其中Sequelize没有正确检测关系 .

我有4张 table ：

Post_code | Building | Flat | Invoice

它们是一对多关系，如下图所示：

enter image description here

这是我想要执行的SQL查询：

SELECT 
   CONCAT(building.name, ' ', flat.number) as 'buildingInfo',
   CONCAT(building.address,' ',post_code.city,' ',post_code.code) as 
   'address',
   IFNULL(invoice.date,'Unknonw') as 'lastInvoiceDate'
FROM building
INNER JOIN post_code ON post_code.id = building.post_code_id
INNER JOIN flat ON building.id = flat.building_id
LEFT JOIN invoice ON flat.id = invoice.flat_id;

Sequelize查询：

models.building.findAll({

    attributes: 
    [
       [Sequelize.fn("CONCAT", Sequelize.col("building.name"),' 
       ',Sequelize.col("flats.number")),'buildingInfo'],
       [Sequelize.fn("CONCAT",Sequelize.col("building.address"),' 
       ',Sequelize.col("post_code.city"),' 
       ',Sequelize.col("post_code.code")),'address'],          
       [Sequelize.fn("IFNULL",Sequelize.col("invoice.date"),'Unknown'),
       'lastInvoiceDate']
    ],

    include:
        [
            {
                model: models.flat,
                required: true
            },
            {
                model: models.post_code,
                required: true
            },
            {
                model: models.invoice,
                required: false
            }
        ]

}).then(function (data){
    // Something here
});

问题考虑：“LEFT JOIN发票ON flat.id = invoice.flat_id;”

我收到错误，“发票”与“建筑”无关 .

这是真的，因为这个关系没有在建筑模型中定义，它只与post_code和flat连接，但是当我在做JOIN时，我也可以包括发票表，“通过”平面表 .

如何使用Sequelize包含此表？

1 回答

您的 invoice 模型应包含在 flat 模型中，如下所示 .

{
    model: models.flat,
    required: true,
    include : [{
        model :  model: models.invoice
        required : false
    }]
}

回复于 2024-05-09T07:07:57+08:00

快递js Sequelize查询

1 回答

相关问题