Express js + Sequelize querying

Question

I am using MySQL database in my project. Problem consider complicated query where Sequelize is not detecting relation properly.

I have 4 tables:

Post_code | Building | Flat | Invoice

They are in relation one-many, like in diagram below:

This is SQL query that I want to perform:

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 querying:

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

Problem consider: "LEFT JOIN invoice ON flat.id = invoice.flat_id;"

I am getting error that "invoice" is not in relation with "building".

That is true because this relation is not defined in building model, it has connection only with post_code and flat, but still when I am doing JOIN I can include invoice table as well, "through" flat table.

How I can include this table using Sequelize?

Answer 1

Your invoice model should be included in flat model as follows.

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