续集嵌套关联查询

我有一个具有多链关联的模型,如下所示:

Epci -> have many -> 公社 -> have one -> 离开 ->有一个-> 区域

我在努力回忆 Epci 行及其关联的 Regions 结果是:

[
    {
        id: 1,
        name: 'EPCI 1',
        regions: [         // [1, 2] is also an acceptable format 
            { id: 1 },
            { id: 2 } 
        ]  
    }
]

目前,我取得了如下成果:

    [
        {
            id: 1,
            name: 'EPCI 1',
            communes: [         
                { 
                    id: 1, 
                    departement: {
                        id: 1,
                        region: {
                           id: 1
                        }  
                    } 
                },
                { 
                    id: 2, 
                    departement: {
                        id: 1,
                        region: {
                           id: 1
                        }  
                    } 
                },
                { 
                    id: 3, 
                    departement: {
                        id: 2,
                        region: {
                           id: 2
                        }  
                    } 
                },
            ]  
        }
    ]

通过这个电话:

Epci.findAll({
    attributes: [
        'id',
        'name',
    ],
    include: [{
        model: Commune,
        attributes: ['id'],
        include: [{
            model: Departement,
            attributes: ['id'],
            include: [{
                model: Region,
                attributes: ['id'],
            }],
        }],
    }],
})

有没有一种方法可以直接检索相关的(和不同的) 区域 使用 findAll 作用试图使用 Virtual 列和某种别名,但没有成功。。。

(我试图避免对每个检索到的行运行查询)

当前临时(?)解决方案(顺便说一句,使用Sequelize TypeScript):

当前的目标是检索一个简单的区域数组,并清除Sequelize返回的“commons”树属性的Epci结果。

因此,我们执行请求并检索所需的数据,然后用Javascript转换结果。

Epci.findAll({
    attributes: [
        'id',
        'name',
    ],
    include: [{
        model: Commune,
        attributes: ['id'],
        include: [{
            model: Departement,
            attributes: ['id'],
            include: [{
                model: Region,
                attributes: ['id'],
            }],
        }],
    }],
}).map((row: Epci) => {
    // Retrieve plain object 
    const plain = row.get({ plain: true });

    // Rebuild (retreive, unique) the regions property (declared as Virtual in the Epci model -- important).
    plain.regions = plain.communes.map(commune => {
        return commune.departement.region.id;
    }).filter((id, index, self) => {
        return self.indexOf(id) === index;
    });
    // Remove the communes property.
    delete plain.communes;

    // Rebuild Epci instance with the modified plain object
    const instance = this.repository.build(plain);

    // Then return instance 
    return instance;
});

使用Postgresql数组函数的公认且性能良好的解决方案:

sequelize.query(`
    SELECT 
        E.id,
        E.name,
        ARRAY_AGG(DISTINCT R.id) as "regions"
    FROM layer_epci E
    LEFT JOIN layer_commune C ON C.epci_id = E.id
    LEFT JOIN layer_departement D ON C.departement_id= D.id
    LEFT JOIN layer_region R ON D.region_id = R.id
    GROUP BY E.id
`, {
   model: Epci,
   mapToModel: true
}).map((row: Epci) => {
    // Do some crazy stuff with Epci models.
    return epci;
});