CODEWITHSUNDEEP
node.jssequelize.js
Alexandr Zarubkin
Alexandr Zarubkin

Reputation: 1026

Joining same table multiple times with Sequelize

I have the following models:

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

And I'd like to select bikes with their average rates, plus rates of the current user for each bike:

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

It constructs the following query: 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];

And fails: SequelizeDatabaseError: The correlation name 'rates' is specified multiple times in a FROM clause.

How do I write the query right? I need Sequelize to assign another alias to the rates table used in the 2nd join (and add its columns to the GROUP BY clause, but that's the next step).

Upvotes: 5

Views: 11183

Answers (3)

Abhishek Shah
Abhishek Shah

Reputation: 864

You can do multiple inner joins with same table by adding extra same association with that model but with a different alias that is as: 'alias1' , as: 'alias2' ,... - all this existing with the same model + same type of association.
Also posted this solution at github issue: https://github.com/sequelize/sequelize/issues/7754#issuecomment-783404779

E.g. for Chats that have many Receiver
Associations (Duplicating for as many needed)

Chat.hasMany(Receiver, {
    // foreignKey: ...
    as: 'chatReceiver',
});
Chat.hasMany(Receiver, {
    // foreignKey: ...
    as: 'chatReceiver2',
});

Now you are left to include associated model multiple times all with different alias so it does not gets overridden.
So you can use them in query as below:

Chat.findAll({
    attributes: ["id"],
    include: [{
        required: true,
        model: Receiver,
        as: 'chatReceiver', // Alias 1
        attributes: [],
        where: { userID: 1 }, // condition 1
    }, {
        required: true,
        model: Receiver,
        as: 'chatReceiver2', // Alias 2
        attributes: [],
        where: { userID: 2 }, // condition 2 as needed
    }]
});

Upvotes: 2

Tomty
Tomty

Reputation: 2022

Sequelize doesn't support including through the same association twice (see here, here, and here). At the model level, you can define 2 different associations between Bike and Rate, but having to change the model, adding new foreign keys etc, is a very hacky solution.

Incidentally, it wouldn't solve your other problem, which is that you're grouping only by Bike but then want to select the user's rate. To fix that, you'd also have to change your grouping to include the user rates. (Note that if a user has more than 1 rate per bike, that might also create some inefficiency, as the rates for the bike are averaged repeatedly for each of the user's rates.)

A proper solution would be using window functions, first averaging the rates per bike and then filtering out all the rates not belonging to the logged in user. Might look something like this:

SELECT *
FROM (
    SELECT bike.*,
        users.login AS user_login,
        AVG (rates.rate) OVER (PARTITION BY bike.id) AS rate_avg
    FROM bike
    INNER JOIN rates ON rates.bikeId = bike.id
    INNER JOIN users ON rates.userId = users.id
)
WHERE user_login = :req_user_login

Unfortunately, as far as I'm aware sequelize doesn't currently support subqueries in the FROM clause and using window functions in this way, so you'd have to fall back to a raw query.

Upvotes: 1

Vivek Doshi
Vivek Doshi

Reputation: 58553

Solution :

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

NOTE : READ THE COMMENTS

Upvotes: 1

Related Questions