时间戳

首页 > 解决方案 > 使用 Sequelize 多次加入同一个表

node.js - 使用 Sequelize 多次加入同一个表

问题描述

我有以下型号：

const User = Sequelize.define('user', {
    login: Sequelize.DataTypes.STRING,
    password: Sequelize.DataTypes.STRING,
    is_manager: Sequelize.DataTypes.BOOLEAN,
    notes: Sequelize.DataTypes.STRING
});

const Bike = Sequelize.define('bike', {
    model: Sequelize.DataTypes.STRING,
    photo: Sequelize.DataTypes.BLOB,
    color: Sequelize.DataTypes.STRING,
    weight: Sequelize.DataTypes.FLOAT,
    location: Sequelize.DataTypes.STRING,
    is_available: Sequelize.DataTypes.BOOLEAN
});

const Rate = Sequelize.define('rate', {
    rate: Sequelize.DataTypes.INTEGER
});
Rate.belongsTo(User);
User.hasMany(Rate);
Rate.belongsTo(Bike);
Bike.hasMany(Rate);

我想选择自行车的平均价格，加上每辆自行车当前用户的价格：

    Bike.findAll({
        attributes: {include: [[Sequelize.fn('AVG', Sequelize.col('rates.rate')), 'rate_avg']],
        },
        include: [{
            model: Rate,
            attributes: []
        }, {
            model: Rate,
            attributes: ['rate'],
            include: [{
                model: User,
                attributes: [],
                where: {
                    login: req.user.login
                }
            }]
        }],
        group: Object.keys(Bike.rawAttributes).map(key => 'bike.' + key) // group by all fields of Bike model
    })

它构造以下查询：SELECT [bike].[id], [bike].[model], [bike].[photo], [bike].[color], [bike].[weight], [bike].[location], [bike].[is_available], AVG([rates].[rate]) AS [rate_avg], [rates].[id] AS [rates.id], [rates].[rate] AS [rates.rate] FROM [bikes] AS [bike] LEFT OUTER JOIN [rates] AS [rates] ON [bike].[id] = [rates].[bikeId] LEFT OUTER JOIN ( [rates] AS [rates] INNER JOIN [users] AS [rates->user] ON [rates].[userId] = [rates->user].[id] AND [rates->user].[login] = N'user' ) ON [bike].[id] = [rates].[bikeId] GROUP BY [bike].[id], [bike].[model], [bike].[photo], [bike].[color], [bike].[weight], [bike].[location], [bike].[is_available];

并失败：SequelizeDatabaseError: The correlation name 'rates' is specified multiple times in a FROM clause.

如何正确编写查询？我需要 Sequelize 为rates第二次连接中使用的表分配另一个别名（并将其列添加到GROUP BY子句中，但这是下一步）。

标签： node.jssequelize.js

解决方案

解决方案 ：

Bike.findAll({
        attributes: {include: [[Sequelize.fn('AVG', Sequelize.col('rates.rate')), 'rate_avg']],
    },
    include: [{
        model: Rate,
        attributes: []
    }, {
        model: Rate,
        required : false , // 1. just to make sure not making inner join
        separate : true , // 2. will run query separately , so your issue will be solved of multiple times 
        attributes: ['rate'],
        include: [{
            model: User,
            attributes: [],
            where: {
                login: req.user.login
            }
        }]
        group : [] // 3. <------- This needs to be managed , so please check errors and add fields as per error
    }],
    group: Object.keys(Bike.rawAttributes).map(key => 'bike.' + key) // group by all fields of Bike model
})

注意：阅读评论

推荐阅读